Getting #NUM! result for SumProduct

[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.

 

[Dave Peterson]

If you're using xl2003 or below, you can't use the whole column.

 

[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

 

[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?

 

[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.

 

[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.>>>>>>

 

[Peo Sjoblom]

You can even remove the last parentheses


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

 

[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.

 

[PCLIVE]

Correct.

Thank you.

--

You can even remove the last parentheses


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

 

[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?

 

[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!