Formula Problem

[Kell2604]

I am having trouble with a formula. Most of the time it calculates
correctly. It is a sumproduct formula which is supposed to count cells in a
range which are greater than 0 if the cells in a different range (column)
contain a certain word. Ie. count all cells in range XX if cells in range
YY = specific text. For some reason it does not count a few lines in the
spreadsheet. It comes back with 32 when it should show 33 etc. I have
narrowed down which rows are a problem - I'm just wondering if anyone has any
ideas why the formula would not count a certain row. Is it a formatting
issue? What things should I check for?? What kinds of things would cause it
to bring back an incorrect value?

Thanks very much!

Formula: =SUMPRODUCT(--('Shipped against rqd Nov08'!$AH$4:$AH$527="Bus Jet
OEM"),--('Shipped against rqd Nov08'!AP$4:AP$527>0))

 

[Gary''s Student]

It is probably a value that LOOKS like it is greater than zero, but is
actually not.

 

[David Biddulph]

Are you sure that the relevant entry in column AH contains "Bus Jet OEM" and
nothing else? Are there odd spaces or other non-printing characters?
Does =LEN(AH4) or whatever the row number is, show 11?

If you are still struggling you can debug the two halves separately by using
the formulae
=('Shipped against rqd Nov08'!AH4="Bus Jet OEM")
and =('Shipped against rqd Nov08'!AP4>0)
and copying them down the columns checking for TRUE or FALSE

 

[Kell2604]

Can you explain how that would be? I have sperated out 2 rows. One which
the formula counts and the other it does not. They both have the number 2.2
in the cell to be counted. If I check the formatting they are both formatted
as a number with one decimal. If I do a customer filter for all rows with a
cell value greater than 0 they both come up.

 

[Gary''s Student]

Very good.

The next thing to look for is text problems:

"Bus Jet OEM "
instead of:
"Bus Jet OEM"

 

[Kell2604]

Thanks guys for all of your help. I haven't figured it out yet, but I'll
keep plugging away at it. It is something in the text field. I have been
able to rule the number out.

Thanks again!!
Kelley

 

[Shane Devenshire]

Hi,

FYI, if you made a pivot table from this data you would have spotted the
type instantly because the pivot table would create two categories which
"looked" the same. And the only time the pivot table creates a different
category is when the items are different. In other words pivot tables act
like they incorporate spell checking.

Cheers,
Shane Devenshire