Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
You can create a helper column which holds the year and change the formula to
reference the helper column. Assume the helper column is column D: =SUMPRODUCT((YEAR(B17:B53)=H$1)*C17:C53)) Dave -- Brevity is the soul of wit. "Bumblebee" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
Thanks but that is exactly what I was trying to do but didn't work. I guess I
really didn't make myself understood. What works is what Biff suggested. "Dave F" wrote: You can create a helper column which holds the year and change the formula to reference the helper column. Assume the helper column is column D: =SUMPRODUCT((YEAR(B17:B53)=H$1)*C17:C53)) Dave -- Brevity is the soul of wit. "Bumblebee" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct in a given year
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 |
#9
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) |