Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT
I'm using the formula below to calculate the values in each row. Its not
working and I'm stummped as to why. Note that there are blank cells in the ranges listed, whether or not that makes a difference I dont know. The sheet is formatted as follows.. =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536) A B C 7-JAN 10 10 12-JAN 5 8 6-MAR 4 6 22-MAR 11 11 3-May 7 9 17-MAY 8 8 I need it to calculate only the data in columns B & C that are equal and then total that data from only column B that matches column C while breaking it down by month. So in the example above the correct value returned from the formula should be Jan 10 Mar 11 May 8 Help in what I'm doing wrong it appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT
Hi,
Use =SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536) for Jan =SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536) for Mar and so on. regards Govind. Aaron wrote: I'm using the formula below to calculate the values in each row. Its not working and I'm stummped as to why. Note that there are blank cells in the ranges listed, whether or not that makes a difference I dont know. The sheet is formatted as follows.. =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536) A B C 7-JAN 10 10 12-JAN 5 8 6-MAR 4 6 22-MAR 11 11 3-May 7 9 17-MAY 8 8 I need it to calculate only the data in columns B & C that are equal and then total that data from only column B that matches column C while breaking it down by month. So in the example above the correct value returned from the formula should be Jan 10 Mar 11 May 8 Help in what I'm doing wrong it appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT
You can't use the whole column.
I'd try to limit those ranges to just what you need (plus a bit more -- just in case). Aaron wrote: I'm using the formula below to calculate the values in each row. Its not working and I'm stummped as to why. Note that there are blank cells in the ranges listed, whether or not that makes a difference I dont know. The sheet is formatted as follows.. =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536) A B C 7-JAN 10 10 12-JAN 5 8 6-MAR 4 6 22-MAR 11 11 3-May 7 9 17-MAY 8 8 I need it to calculate only the data in columns B & C that are equal and then total that data from only column B that matches column C while breaking it down by month. So in the example above the correct value returned from the formula should be Jan 10 Mar 11 May 8 Help in what I'm doing wrong it appreciated. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT
Thanks a ton... it works.
Now, I didnt think about this before, but what if I wanted to do the same thing only break it apart by month/year Ex: Jan-03 = x Feb-03 =x Jan-05 =x Feb-05 =x Thanks for the help. "Govind" wrote: Hi, Use =SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536) for Jan =SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536) for Mar and so on. regards Govind. Aaron wrote: I'm using the formula below to calculate the values in each row. Its not working and I'm stummped as to why. Note that there are blank cells in the ranges listed, whether or not that makes a difference I dont know. The sheet is formatted as follows.. =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536) A B C 7-JAN 10 10 12-JAN 5 8 6-MAR 4 6 22-MAR 11 11 3-May 7 9 17-MAY 8 8 I need it to calculate only the data in columns B & C that are equal and then total that data from only column B that matches column C while breaking it down by month. So in the example above the correct value returned from the formula should be Jan 10 Mar 11 May 8 Help in what I'm doing wrong it appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT
Hi,
Just add one more condition for year. For eg =SUMPRODUCT(--(MONTH(A2:A65536)=1),--(YEAR(A2:A65536)=2005),--(B2:B65536=C2:C65536),B2:B65536) for Jan 2005 Regards Govind. Aaron Saulisberry wrote: Thanks a ton... it works. Now, I didnt think about this before, but what if I wanted to do the same thing only break it apart by month/year Ex: Jan-03 = x Feb-03 =x Jan-05 =x Feb-05 =x Thanks for the help. "Govind" wrote: Hi, Use =SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536) for Jan =SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536) for Mar and so on. regards Govind. Aaron wrote: I'm using the formula below to calculate the values in each row. Its not working and I'm stummped as to why. Note that there are blank cells in the ranges listed, whether or not that makes a difference I dont know. The sheet is formatted as follows.. =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536) A B C 7-JAN 10 10 12-JAN 5 8 6-MAR 4 6 22-MAR 11 11 3-May 7 9 17-MAY 8 8 I need it to calculate only the data in columns B & C that are equal and then total that data from only column B that matches column C while breaking it down by month. So in the example above the correct value returned from the formula should be Jan 10 Mar 11 May 8 Help in what I'm doing wrong it appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |