sumproduct with boolean criteria returns unexpected 0

  • Thread starter Thread starter goss
  • Start date Start date
G

goss

Hi all -

My sumproduct formula is measuring 2 criteria
-Values < 0?
-Period = P1

But it is returning a #N/A error
Can anyone give me a nudge in the right direction?

=SUMPRODUCT((Data!$D$2:$D$2000>0)*(Data!$E$2:$E$2000=Summary!$K$2))/
1000

Thanks.
Best regards,
markc
 
It works for me. Have a look at the data itself, has it been imported from
somewhere? It's hard to say without looking at it but your formula is sound.
Regards,
Alan.
 
Hmmm...

Well, your subject line says you're getting a result of 0 and your post says
you're getting a result of #N/A.

If the result is 0:
-Values < 0?
(Data!$D$2:$D$2000>0)
Click to expand...

It may be due to use of the wrong operator. > or < ?

If the result is #N/A:

There's nothing wrong with the formula itself. Are there any #N/A errors in
either of the ranges?
 
Hmmm...

Well, your subject line says you're getting a result of 0 and your post says
you're getting a result of #N/A.

If the result is 0:


It may be due to use of the wrong operator. > or < ?

If the result is #N/A:

There's nothing wrong with the formula itself. Are there any #N/A errors in
either of the ranges?

--
Biff
Microsoft Excel MVP








- Show quoted text -
Click to expand...

Apolgies on confusion with subect and body
Originally I was getting #N/A, then relized the second argument in the
formula did not use $2000 rows only $45 rows
I change to $2000 and now I receive a 0

Alan -

The data has been copy pasted to Excel from HTML then
data..text..columns
I also ran a macro to clean all extra spaces and another macro to
bring any trailing negatives to the fron of the number
I tested the value column with =ISNUMBER(). All returned TRUE.

I then picked a single period of the dataset and switched to a very
simple sumif formula:
=SUMIF(Data!$D$24:$D$45,"<0")/1000

This returned a value of 2.0 (K's)

I then manually added all items in the range < 0.
I received a result of $2,011.65.

All that said, I believe the data is good to go.
Has to be something with the formula

Best regards,
-markc
 
goss said:
Apolgies on confusion with subect and body
Originally I was getting #N/A, then relized the second argument in the
formula did not use $2000 rows only $45 rows
I change to $2000 and now I receive a 0

Alan -

The data has been copy pasted to Excel from HTML then
data..text..columns
I also ran a macro to clean all extra spaces and another macro to
bring any trailing negatives to the fron of the number
I tested the value column with =ISNUMBER(). All returned TRUE.

I then picked a single period of the dataset and switched to a very
simple sumif formula:
=SUMIF(Data!$D$24:$D$45,"<0")/1000

This returned a value of 2.0 (K's)

I then manually added all items in the range < 0.
I received a result of $2,011.65.

All that said, I believe the data is good to go.
Has to be something with the formula

Best regards,
-markc
Click to expand...

The formula is syntactically correct so it has to be a data problem.

If the data in Data!$D$2:$D$2000 all checked out OK to be numbers then what
about the other range Data!$E$2:$E$2000 and Summary!$K$2.

You said you ran a macro to "clean all extra spaces". Does that macro clean
the char 160 non breaking spaces commonly found in html?

There is a macro at this location that cleans those char 160's:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
 
The formula is syntactically correct so it has to be a data problem.

If the data in Data!$D$2:$D$2000 all checked out OK to be numbers then what
about the other range Data!$E$2:$E$2000 and Summary!$K$2.

You said you ran a macro to "clean all extra spaces". Does that macro clean
the char 160 non breaking spaces commonly found in html?

There is a macro at this location that cleans those char 160's:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Biff,
It is Dave McRitchie;s Trimall macro I use after converting data from
HTML,.txt, and .pdf
Originally the formula was a sumif formula, I edited into the
sumproduct formula.

I went to the data sheet below the data and wrote a sumproduct formula
from scratch which worked
I went back to the summary sheet and wrote a sumproduct formula from
scratch which worked.

I guess the lesson learned is if you don't trust the results, start
over from scratch

Thanks
Best regards,
-markc
 
goss said:
Thanks Biff,
It is Dave McRitchie;s Trimall macro I use after converting data from
HTML,.txt, and .pdf
Originally the formula was a sumif formula, I edited into the
sumproduct formula.

I went to the data sheet below the data and wrote a sumproduct formula
from scratch which worked
I went back to the summary sheet and wrote a sumproduct formula from
scratch which worked.

I guess the lesson learned is if you don't trust the results, start
over from scratch

Thanks
Best regards,
-markc
Click to expand...

Glad to hear you got it straightened out. Thanks for the feedback!
 
Back
Top