sumproduct with boolean criteria returns unexpected 0

[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

 

[Alan]

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.

 

[T. Valko]

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)

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?

 

[goss]

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 -

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

 

[T. Valko]

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

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

 

[goss]

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 -

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

 

[T. Valko]

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

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