Thread: 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 <4E4988CB-0AAC-45C0-B4CC-361E9AF59227@microsoft.com>,
"Dave Davis" <Dave Davis@discussions.microsoft.com> 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 <4E4988CB-0AAC-45C0-B4CC-361E9AF59227@microsoft.com>,
> "Dave Davis" <Dave Davis@discussions.microsoft.com> 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?
>