software

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 amp; 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.
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:
gt; I'm using the formula below to calculate the values in each row. Its not
gt; working and I'm stummped as to why. Note that there are blank cells in the
gt; ranges listed, whether or not that makes a difference I dont know. The sheet
gt; is formatted as follows..
gt;
gt; =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536)
gt;
gt;
gt; A B C
gt; 7-JAN 10 10
gt; 12-JAN 5 8
gt; 6-MAR 4 6
gt; 22-MAR 11 11
gt; 3-May 7 9
gt; 17-MAY 8 8
gt;
gt; I need it to calculate only the data in columns B amp; C that are equal and
gt; then total that data from only column B that matches column C while breaking
gt; it down by month.
gt;
gt; So in the example above the correct value returned from the formula should
gt; be
gt;
gt; Jan 10
gt; Mar 11
gt; May 8
gt;
gt; Help in what I'm doing wrong it appreciated.
gt;
gt;

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:
gt;
gt; I'm using the formula below to calculate the values in each row. Its not
gt; working and I'm stummped as to why. Note that there are blank cells in the
gt; ranges listed, whether or not that makes a difference I dont know. The sheet
gt; is formatted as follows..
gt;
gt; =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536)
gt;
gt; A B C
gt; 7-JAN 10 10
gt; 12-JAN 5 8
gt; 6-MAR 4 6
gt; 22-MAR 11 11
gt; 3-May 7 9
gt; 17-MAY 8 8
gt;
gt; I need it to calculate only the data in columns B amp; C that are equal and
gt; then total that data from only column B that matches column C while breaking
gt; it down by month.
gt;
gt; So in the example above the correct value returned from the formula should
gt; be
gt;
gt; Jan 10
gt; Mar 11
gt; May 8
gt;
gt; Help in what I'm doing wrong it appreciated.

--

Dave Peterson

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.

quot;Govindquot; wrote:

gt; Hi,
gt;
gt; Use
gt;
gt; =SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536)
gt; for Jan
gt;
gt; =SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536)
gt; for Mar
gt;
gt; and so on.
gt;
gt;
gt; regards
gt;
gt; Govind.
gt;
gt; Aaron wrote:
gt; gt; I'm using the formula below to calculate the values in each row. Its not
gt; gt; working and I'm stummped as to why. Note that there are blank cells in the
gt; gt; ranges listed, whether or not that makes a difference I dont know. The sheet
gt; gt; is formatted as follows..
gt; gt;
gt; gt; =SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536)
gt; gt;
gt; gt;
gt; gt; A B C
gt; gt; 7-JAN 10 10
gt; gt; 12-JAN 5 8
gt; gt; 6-MAR 4 6
gt; gt; 22-MAR 11 11
gt; gt; 3-May 7 9
gt; gt; 17-MAY 8 8
gt; gt;
gt; gt; I need it to calculate only the data in columns B amp; C that are equal and
gt; gt; then total that data from only column B that matches column C while breaking
gt; gt; it down by month.
gt; gt;
gt; gt; So in the example above the correct value returned from the formula should
gt; gt; be
gt; gt;
gt; gt; Jan 10
gt; gt; Mar 11
gt; gt; May 8
gt; gt;
gt; gt; Help in what I'm doing wrong it appreciated.
gt; gt;
gt; gt;
gt;

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:
gt; Thanks a ton... it works.
gt;
gt; Now, I didnt think about this before, but what if I wanted to do the same
gt; thing only break it apart by month/year
gt;
gt; Ex:
gt;
gt; Jan-03 = x
gt; Feb-03 =x
gt; Jan-05 =x
gt; Feb-05 =x
gt;
gt; Thanks for the help.
gt;
gt; quot;Govindquot; wrote:
gt;
gt;
gt;gt;Hi,
gt;gt;
gt;gt;Use
gt;gt;
gt;gt;=SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536)
gt;gt;for Jan
gt;gt;
gt;gt;=SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536)
gt;gt;for Mar
gt;gt;
gt;gt;and so on.
gt;gt;
gt;gt;
gt;gt;regards
gt;gt;
gt;gt;Govind.
gt;gt;
gt;gt;Aaron wrote:
gt;gt;
gt;gt;gt;I'm using the formula below to calculate the values in each row. Its not
gt;gt;gt;working and I'm stummped as to why. Note that there are blank cells in the
gt;gt;gt;ranges listed, whether or not that makes a difference I dont know. The sheet
gt;gt;gt;is formatted as follows..
gt;gt;gt;
gt;gt;gt;=SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536)
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; A B C
gt;gt;gt; 7-JAN 10 10
gt;gt;gt; 12-JAN 5 8
gt;gt;gt; 6-MAR 4 6
gt;gt;gt; 22-MAR 11 11
gt;gt;gt; 3-May 7 9
gt;gt;gt; 17-MAY 8 8
gt;gt;gt;
gt;gt;gt;I need it to calculate only the data in columns B amp; C that are equal and
gt;gt;gt;then total that data from only column B that matches column C while breaking
gt;gt;gt;it down by month.
gt;gt;gt;
gt;gt;gt;So in the example above the correct value returned from the formula should
gt;gt;gt;be
gt;gt;gt;
gt;gt;gt;Jan 10
gt;gt;gt;Mar 11
gt;gt;gt;May 8
gt;gt;gt;
gt;gt;gt;Help in what I'm doing wrong it appreciated.
gt;gt;gt;
gt;gt;gt;
gt;gt;