Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I have a spreadsheet that looks something like:
Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07 Row B 4 2 5 .... 9 I would like to sum everthing in row B within a certain month. I have tried: =SUMPRODUCT(Month(A1:A365)=1,B1:B365) This is not working. Would someone tell me what I'm missing? Thanks so much. Ellen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
=sumproduct() likes numbers:
=SUMPRODUCT(--(Month(A1:A365)=1),B1:B365) But be careful. An empty cell will look like it has a month() of january. =SUMPRODUCT(--(Month(A1:A365)=1),--(a1:a365<""),B1:B365) might be better. Ellen G wrote: I have a spreadsheet that looks something like: Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07 Row B 4 2 5 .... 9 I would like to sum everthing in row B within a certain month. I have tried: =SUMPRODUCT(Month(A1:A365)=1,B1:B365) This is not working. Would someone tell me what I'm missing? Thanks so much. Ellen -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Okay -- sorry. I typed the formula in my original message incorrectly. Here
is the type of formula I've actually been using that is not working: =SUMPRODUCT(Month(A1:Z1)=1,A2:Z2) What am I doing wrong??? Ellen "Ellen G" wrote: I have a spreadsheet that looks something like: Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07 Row B 4 2 5 .... 9 I would like to sum everthing in row B within a certain month. I have tried: =SUMPRODUCT(Month(A1:A365)=1,B1:B365) This is not working. Would someone tell me what I'm missing? Thanks so much. Ellen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
=SUMPRODUCT(-(Month(A1:Z1)=1,A2:Z2)
or =SUMPRODUCT((Month(A1:Z1)=1)*A2:Z2) -- Don Guillett SalesAid Software "Ellen G" wrote in message ... Okay -- sorry. I typed the formula in my original message incorrectly. Here is the type of formula I've actually been using that is not working: =SUMPRODUCT(Month(A1:Z1)=1,A2:Z2) What am I doing wrong??? Ellen "Ellen G" wrote: I have a spreadsheet that looks something like: Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07 Row B 4 2 5 .... 9 I would like to sum everthing in row B within a certain month. I have tried: =SUMPRODUCT(Month(A1:A365)=1,B1:B365) This is not working. Would someone tell me what I'm missing? Thanks so much. Ellen |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Thanks so much, Dave. This helped a lot.
Ellen "Dave Peterson" wrote: =sumproduct() likes numbers: =SUMPRODUCT(--(Month(A1:A365)=1),B1:B365) But be careful. An empty cell will look like it has a month() of january. =SUMPRODUCT(--(Month(A1:A365)=1),--(a1:a365<""),B1:B365) might be better. Ellen G wrote: I have a spreadsheet that looks something like: Row A 1/1/07 1/2/07 1/3/07 .... 12/31/07 Row B 4 2 5 .... 9 I would like to sum everthing in row B within a certain month. I have tried: =SUMPRODUCT(Month(A1:A365)=1,B1:B365) This is not working. Would someone tell me what I'm missing? Thanks so much. Ellen -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct | Excel Discussion (Misc queries) | |||
Like Sumproduct, But Different | Excel Worksheet Functions | |||
Sumproduct Help | Excel Worksheet Functions | |||
MAX/MIN with SUMPRODUCT | Excel Worksheet Functions |