Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif for months and year
a b c d e
----------------------------------------------------------------------------- 1| 13-Nov-06 20-Nov-06 27-Nov-06 4-Dec-06 11-Dec-06 2| 1 2 3 4 5 3| 4| November, 06 5| 0 I need cell a5 to sum a2:e2 if the month and year on a1:e1 match a4. I tried =SUMIF(a1:e1,"*Nov*",a2:e2) and =SUMPRODUCT(--(a1:e1=DATE(YEAR(a4),MONTH(a4),b2:e2))) Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif for months and year
=SUMPRODUCT(--(YEAR(A1:E1)=YEAR(A4)),--(MONTH(A1:E1)=MONTH(A4)),A2:E2)
note that you shouldn't have text in A4, it needs to be a date although you can custom format is as mmmm, yy -- Regards, Peo Sjoblom "Kevin" wrote in message ... a b c d e ----------------------------------------------------------------------------- 1| 13-Nov-06 20-Nov-06 27-Nov-06 4-Dec-06 11-Dec-06 2| 1 2 3 4 5 3| 4| November, 06 5| 0 I need cell a5 to sum a2:e2 if the month and year on a1:e1 match a4. I tried =SUMIF(a1:e1,"*Nov*",a2:e2) and =SUMPRODUCT(--(a1:e1=DATE(YEAR(a4),MONTH(a4),b2:e2))) Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif for months and year
Wheeewww, that works!! Thanks so much
"Peo Sjoblom" wrote: =SUMPRODUCT(--(YEAR(A1:E1)=YEAR(A4)),--(MONTH(A1:E1)=MONTH(A4)),A2:E2) note that you shouldn't have text in A4, it needs to be a date although you can custom format is as mmmm, yy -- Regards, Peo Sjoblom "Kevin" wrote in message ... a b c d e ----------------------------------------------------------------------------- 1| 13-Nov-06 20-Nov-06 27-Nov-06 4-Dec-06 11-Dec-06 2| 1 2 3 4 5 3| 4| November, 06 5| 0 I need cell a5 to sum a2:e2 if the month and year on a1:e1 match a4. I tried =SUMIF(a1:e1,"*Nov*",a2:e2) and =SUMPRODUCT(--(a1:e1=DATE(YEAR(a4),MONTH(a4),b2:e2))) Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Year-Days-Months | Excel Worksheet Functions | |||
Within 6 months, Within 1 year and so on... | Excel Discussion (Misc queries) | |||
Locate particular months sale with year | New Users to Excel | |||
Calculating by individual months of the year | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) |