| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
You're welcome!
Biff "Bumblebee" wrote in message ... Thank you again "Biff" wrote: If you're absolutely sure there are only 12 entries (and will always be only 12 entries) that meet the YEAR criteria then just add this to the end of the formula: /12 Just to be on the safe side I would use this array formula. Entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(YEAR(B17:B53)=YEAR(A1),C17:C53)) Biff "Bumblebee" wrote in message ... One more question if you don't mind, if instead of the sum I want the average of the twelve entries (you see there is one for each month of the year) would I have to stick AVERAGE somewhere in the formula or would it be a different function altogether "Bumblebee" wrote: Thanks it worked "Biff" wrote: Hi! Try this: A1 = 31/12/2005 =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53) Biff "Bumblebee" wrote in message ... Hi, can someone help me with this: the following formula works: SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53)) if I stick in the year in the formula, e.g. 2005 as in above. But when I reference it to another cell that puts 31/12/2005 I get 0 as an answer. How do I get it to understand that I am interested in the 2005 bit. I don't know if I am making myself understood. I want to sume a list of things in a given year, 2004, 2005, but the year part referenced to another cell that has the date looking like this 31/12/aaaa Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Graphing-Two stacked columns side-by-side per year | Charts and Charting in Excel | |||
| Vacation Time calculation... HELP! | Excel Discussion (Misc queries) | |||
| Lookup returns wrong value | Excel Worksheet Functions | |||
| holiday dates | Excel Worksheet Functions | |||
| How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
Hybrid Mode
