SUMPRODUCT gives incorrect total

[jeannie v]

Hi Experts:

This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince
George"),--('SP TCS REPORT'!I2:I10000="Count of Observations">0))

The total of records should be 182 but this formula displays 179

There is no hidden data and the naming conventions for ACS-Prince George and
Count of Observations are exactly the same through the entire document.

Can you tell me what might be wrong with this formula?

Thank you for any help you can provide,

 

[Pete_UK]

What is the

0

doing at the end of the second term?

I suggest you get rid of this.

Hope this helps.

Pete

 

[jeannie v]

0 is to add all Count of Observations greater than 0....I did take the >0
out and it give me a result of "0".

Any more help you can provide would be appreciated.

 

[jeannie v]

I'm using AutoFilter to pull the records for the site (ACS-Prince
George)...does this make a difference? Would there be a Macro that would
work?

 

[Dave Peterson]

Maybe it's a typo.

If the typo is an extra space character, you could use this version of your
formula:

=SUMPRODUCT(--(trim('SP TCS REPORT'!H2:H10000)="ACS-Prince George"),
--(trim('SP TCS REPORT'!I2:I10000)="Count of Observations"))

If this gives you 182, I'd take the time to fix those cells with the typos.

 

[jeannie v]

Thank you Dave for the formula, however, it doesn't work...It displays "0"..

Any other ideas that would help?

I appreciate any suggestions,

 

[Pete_UK]

And what is "Count of Observations"?

The way I read the formula you were looking for this text in column I
AND for "ACS-Prince George" to be in column H and you wanted to count
how often this happened.

Pete

 

[jeannie v]

Oh...I aplogize for the confusion...No the Count of Observations is Column I
and will have a number of Observations in a record. I want to add the number
of observations in each record....I could be 1, 2, 3, 4 or more observations
for a single record. It want it to total the # of observations in each "I"
Cell and give me a total.

Does that make any sense?

 

[Ron Rosenfeld]

Hi Experts:

This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince
George"),--('SP TCS REPORT'!I2:I10000="Count of Observations">0))

The total of records should be 182 but this formula displays 179

There is no hidden data and the naming conventions for ACS-Prince George and
Count of Observations are exactly the same through the entire document.

Can you tell me what might be wrong with this formula?

Thank you for any help you can provide,

What might be the possible content of, let us say, 'SP TCS REPORT'!I2 and what
are you looking for?

As written, this portion of your formula: ('SP TCS REPORT'!I2:I10000="Count of
Observations">0) will always return a TRUE.

When Excel evaluates the formula, it does so from left to right; so you might
evaluate (short form)

I2="Count of Observations"

This will return either FALSE or TRUE depending on whether that phrase is the
contents of the cell.

The next step is to evaluate either

FALSE>0 or TRUE>0

In either case, the evaluation will be TRUE.

This doesn't make sense as the section, as written, seems entirely superfluous.

You would probably get the same result with the formula:

=COUNTIF('SP TCS REPORT'!H2:H10000,"CS-Prince George")




--ron

 

[Ron Rosenfeld]

Hi Experts:

This is my formula: =SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince
George"),--('SP TCS REPORT'!I2:I10000="Count of Observations">0))

The total of records should be 182 but this formula displays 179

There is no hidden data and the naming conventions for ACS-Prince George and
Count of Observations are exactly the same through the entire document.

Can you tell me what might be wrong with this formula?

Thank you for any help you can provide,

After reading some of your other responses, I wonder if what you really want
is:

=sumif('SP TCS REPORT'!H2:H10000,"ACS-Prince George",'SP TCS REPORT'!I2:I10000)


--ron

 

[jeannie v]

Thank you all...you got me thinking and...I figured out what I was doing
wrong....this is the correct formula to get the correct result.

=SUMPRODUCT(--('SP TCS REPORT'!H2:H10000="ACS-Prince George"),--('SP TCS
REPORT'!I2:I10000))