sumproduct with boolean criteria returns unexpected 0
 

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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc

sumproduct with boolean criteria returns unexpected 0
 

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.
"goss" wrote in message
oups.com...
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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc

sumproduct with boolean criteria returns unexpected 0
 

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$20000)

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


"goss" wrote in message
oups.com...
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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc

sumproduct with boolean criteria returns unexpected 0
 

On Oct 27, 6:17 pm, "T. Valko" wrote:
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$20000)

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

"goss" wrote in message

oups.com...



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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc- Hide quoted text -

- 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

sumproduct with boolean criteria returns unexpected 0
 

"goss" wrote in message
ps.com...
On Oct 27, 6:17 pm, "T. Valko" wrote:
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$20000)

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

"goss" wrote in message

oups.com...



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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc- Hide quoted text -

- 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

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

sumproduct with boolean criteria returns unexpected 0
 

On Oct 27, 7:24 pm, "T. Valko" wrote:
"goss" wrote in message

ps.com...





On Oct 27, 6:17 pm, "T. Valko" wrote:
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$20000)

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

"goss" wrote in message

groups.com...

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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc- Hide quoted text -

- 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

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

sumproduct with boolean criteria returns unexpected 0
 

"goss" wrote in message
ps.com...
On Oct 27, 7:24 pm, "T. Valko" wrote:
"goss" wrote in message

ps.com...





On Oct 27, 6:17 pm, "T. Valko" wrote:
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$20000)

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

"goss" wrote in message

groups.com...

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$20000)*(Data!$E$2:$E$20 00=Summary!$K$2))/
1000

Thanks.
Best regards,
markc- Hide quoted text -

- 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

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


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

--
Biff
Microsoft Excel MVP