Getting #NUM! result for SumProduct

S

Sarah (OGI)

I have entered the following expression:
=SUMPRODUCT(--(Sheet2!B:B>=D2),--(Sheet2!B:B<C2),--(Sheet2!H:H))

D2 shows 01/09/2005 and C2 shows 01/09/2006.

I have some data on Sheet 2 with data listed as follows:
Date/Class Code/Company Name/Amount

I would like to sum the amounts where the dates are between D2 and C2.

I'm getting a result of #NUM!

Any suggestions? I'm not sure I'm using the right function, but I'm asking
for multiple criteria, i.e. where the date is >= one date but < another date.
 
P

PCLIVE

You cannot use entire columns with SUMPRODUCT.

Specify your range.
=SUMPRODUCT(--(Sheet2!B1:B1000>=D2),--(Sheet2!B1:B1000<C2),--(Sheet2!H1:H1000))

HTH,
Paul
 
S

Sarah (OGI)

Paul

I've amended my formula slightly (and included another criteria). I'm now
just getting a zero, despite knowing that there are figures greater than zero
in the sum_range.

Any ideas?
 
J

JLatham

In response both to your first post and the note that now you're getting zero
even though you think you shouldn't be:

In first post you said Sheet2 has data listed as Date | Class Code | Company
Name | Amount

We need to know for certain that column B on Sheet2 contains your dates?
Also, make sure all apparent dates are really dates and not text entries that
just happen to look like dates.
 
P

PCLIVE

You can actuall emove the "--" in the last part of the formula.

=SUMPRODUCT(--(Sheet2!B1:B1000>=D2),--(Sheet2!B1:B1000<C2),(Sheet2!H1:H1000))However, I don't think that is the cause of the result.What is the cell format for column H and cells C2 and D2?Are all of them in Date formats?--"Sarah (OGI)" <[email protected]> wrote in messagePaul>> I've amended my formula slightly (and included another criteria). I'm now> just getting a zero, despite knowing that there are figures greater thanzero> in the sum_range.>> Any ideas?>> "PCLIVE" wrote:>>> You cannot use entire columns with SUMPRODUCT.>>>> Specify your range.>>=SUMPRODUCT(--(Sheet2!B1:B1000>=D2),--(Sheet2!B1:B1000<C2),--(Sheet2!H1:H1000))>>>> HTH,>> Paul>>>> -->>>> "Sarah (OGI)" <[email protected]> wrote in message>> >I have entered the following expression:>> > =SUMPRODUCT(--(Sheet2!B:B>=D2),--(Sheet2!B:B<C2),--(Sheet2!H:H))>> >>> > D2 shows 01/09/2005 and C2 shows 01/09/2006.>> >>> > I have some data on Sheet 2 with data listed as follows:>> > Date/Class Code/Company Name/Amount>> >>> > I would like to sum the amounts where the dates are between D2 and C2.>> >>> > I'm getting a result of #NUM!>> >>> > Any suggestions? I'm not sure I'm using the right function, but I'm>> > asking>> > for multiple criteria, i.e. where the date is >= one date but < another>> > date.>>>>>>
 
P

Peo Sjoblom

You can even remove the last parentheses


=SUMPRODUCT(--(Sheet2!B1:B1000>=D2),--(Sheet2!B1:B1000<C2),Sheet2!H1:H1000)
 
D

David Biddulph

If you can't understand where you've gone wrong, break your formula down a
bit at a time.

For example, if you think that row 99 satifies your criteria, look at
=(Sheet2!B99>=D2), and at =(Sheet2!B99<C2). Also see what numbers you get
for
=Sheet2!B99-D2, and for =Sheet2!B99-C2.

Also check that what you think are dates are really dates & not text. Try
changing the date format and see whether the display in the relevant cell
changes, or try =ISNUMBER(D2) and =ISNUMBER(Sheet2!B99).

One minor point: if column H has numbers in it, you shouldn't need the
double unary minus, but it oughtn't to do any harm.
 
P

PCLIVE

Correct.

Thank you.

--

Peo Sjoblom said:
You can even remove the last parentheses


=SUMPRODUCT(--(Sheet2!B1:B1000>=D2),--(Sheet2!B1:B1000<C2),Sheet2!H1:H1000)
 
S

Sarah (OGI)

Sorry guys, thanks for your help so far, but I've since changed some of the
details on my workbook, so here is how the situation is now. I'm now getting
a result of #Value!

I have 2 worksheets.

Sheet 4 contains info in the following order:
Customer Ref (integer)/Customer Name/Start Date (dd/mm/yyyy format)/Start
Date minus 1 year

Sheet 2 contains info in the following order:
Customer Ref (integer)/Lookup Code/Date (dd/mm/yyyy format)/Class/Company
Name/Product/Company Code/Amount

I have entered the following formula in Sheet 4 to find the customer ref in
Sheet 2 and to sum all the relevant amounts that are within the specified
date range, i.e. 01/09/2005 in D2 and 01/09/2006 in C2
=SUMPRODUCT(--(Sheet2!A2:A15195=A2),--(Sheet2!C2:C15195>=D2),--(Sheet2!C2:C15195<C2),--(Sheet2!H2:H15195))

Any ideas?
 
D

Dave Peterson

You still don't need the -- or the parentheses around that last portion
(H2:H15195).

I'd try that first.

Then if that doesn't clear all the problem, look for errors in any of those
ranges in your formulas. Remember to look in hidden rows, too!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top