| 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) | |||
Linear Mode
