Getting #NUM! result for SumProduct
 

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.

Getting #NUM! result for SumProduct
 

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

Sarah (OGI) wrote:

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

Getting #NUM! result for SumProduct
 

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)" 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.

Getting #NUM! result for SumProduct
 

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?

"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)" 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.

Getting #NUM! result for SumProduct
 

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.

"Sarah (OGI)" wrote:

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.

Getting #NUM! result for SumProduct
 

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)" wrote in ... 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 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)" 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.

Getting #NUM! result for SumProduct
 

You can even remove the last parentheses


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


--
Regards,

Peo Sjoblom


"PCLIVE" wrote in message
...
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)" wrote in
... 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
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)"
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.

Getting #NUM! result for SumProduct
 

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.
--
David Biddulph

"Sarah (OGI)" wrote in message
...
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?

"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)" 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.

Getting #NUM! result for SumProduct
 

Correct.

Thank you.

--

"Peo Sjoblom" wrote in message
...
You can even remove the last parentheses


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


--
Regards,

Peo Sjoblom


"PCLIVE" wrote in message
...
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)" wrote in
... 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
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)"
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.

Getting #NUM! result for SumProduct
 

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?

"Sarah (OGI)" wrote:

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.

Getting #NUM! result for SumProduct
 

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!


Sarah (OGI) wrote:

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?

"Sarah (OGI)" wrote:

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