Another Sumproduct & #N/A problem
 

I have a budget worksheet in which I keep all my spending records, eg on
Sheet 1:
A B C D
Date Amount Category Month
1 1/1/5 20.00 Dining Jan
2 2/1/5 25.00 Telephone Jan
3 3/1/5 30.00 Petrol Jan
4 4/1/5 15.00 Dining Jan

I am using sumproduct to keep a running total on Sheet 2, of my spend
against each category, for example to return the total for "Dining":
=SUMPRODUCT((Sheet1!c2:c4=Sheet2!b2)*(Sheet1!d2:d4 =Sheet2!a2)
*(Sheet1!b2:b4)
A B C D
Jan Feb Mar
1 Dining 35.00 0.00 0.00
2 Telephone 25.00 0.00 0.00
3 Petrol 30.00 0.00 0.00


The problem is that as soon as the ranges checked by SUMPRODUCT contain a
blank cell, it returns #N/A. I would obviously rather have SUMPRODUCT refer
to the whole column, e.g. Sheet1!c:c: so I can carry on entering new data in
Sheet1 without having to amend the ranges in the formula every time. Can
someone assist please?

Rgds
Dave

This can be a problem when you multiply arrays and then apply
SUMPRODUCT, rather than entering the arrays as individual arguments.
Your blanks are probably space characters rather than true blanks, which
the * operator will choke on, but SUMPRODUCT ignores in arguments.

Try:

=SUMPRODUCT(--(Sheet1!C2:C65536=Sheet2!B2),
--(Sheet1!D2:D65536=Sheet2!A2), Sheet1!B2:B65536)


In article ,
"Dave Davis" <Dave wrote:

I have a budget worksheet in which I keep all my spending records, eg on
Sheet 1:
A B C D
Date Amount Category Month
1 1/1/5 20.00 Dining Jan
2 2/1/5 25.00 Telephone Jan
3 3/1/5 30.00 Petrol Jan
4 4/1/5 15.00 Dining Jan

I am using sumproduct to keep a running total on Sheet 2, of my spend
against each category, for example to return the total for "Dining":
=SUMPRODUCT((Sheet1!c2:c4=Sheet2!b2)*(Sheet1!d2:d4 =Sheet2!a2)
*(Sheet1!b2:b4)
A B C D
Jan Feb Mar
1 Dining 35.00 0.00 0.00
2 Telephone 25.00 0.00 0.00
3 Petrol 30.00 0.00 0.00


The problem is that as soon as the ranges checked by SUMPRODUCT contain a
blank cell, it returns #N/A. I would obviously rather have SUMPRODUCT refer
to the whole column, e.g. Sheet1!c:c: so I can carry on entering new data in
Sheet1 without having to amend the ranges in the formula every time. Can
someone assist please?

Many thanks, I'll try that a report back! (By the way, the cells are
definitely blank, they are unused cells so far....)

Many thanks - this worked fine (apart from recalc being a bit slower!)

"JE McGimpsey" wrote:

This can be a problem when you multiply arrays and then apply
SUMPRODUCT, rather than entering the arrays as individual arguments.
Your blanks are probably space characters rather than true blanks, which
the * operator will choke on, but SUMPRODUCT ignores in arguments.

Try:

=SUMPRODUCT(--(Sheet1!C2:C65536=Sheet2!B2),
--(Sheet1!D2:D65536=Sheet2!A2), Sheet1!B2:B65536)


In article ,
"Dave Davis" <Dave wrote:

I have a budget worksheet in which I keep all my spending records, eg on
Sheet 1:
A B C D
Date Amount Category Month
1 1/1/5 20.00 Dining Jan
2 2/1/5 25.00 Telephone Jan
3 3/1/5 30.00 Petrol Jan
4 4/1/5 15.00 Dining Jan

I am using sumproduct to keep a running total on Sheet 2, of my spend
against each category, for example to return the total for "Dining":
=SUMPRODUCT((Sheet1!c2:c4=Sheet2!b2)*(Sheet1!d2:d4 =Sheet2!a2)
*(Sheet1!b2:b4)
A B C D
Jan Feb Mar
1 Dining 35.00 0.00 0.00
2 Telephone 25.00 0.00 0.00
3 Petrol 30.00 0.00 0.00


The problem is that as soon as the ranges checked by SUMPRODUCT contain a
blank cell, it returns #N/A. I would obviously rather have SUMPRODUCT refer
to the whole column, e.g. Sheet1!c:c: so I can carry on entering new data in
Sheet1 without having to amend the ranges in the formula every time. Can
someone assist please?