Sumif Problems

[Guest]

I am using the sumif formula and noticed that it doesn't pick up all the
data. I tried using trim to clear out all spaces in the criteria but still
no luck. It works when I go and write over the criteria any ideas?

Thanks,
Anat

 

[Nick Hodge]

Anat

I suspect the 'criteria' is seen as text. If these are 'numbers' then
enter a 1 in a spare cell and copy it.

Highlight your 'numbers' and take edit>Paste special...>Values + Multiply.
This should kick Excel into recognising them

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Nick,

The criteria is a text not a number.

ANat

 

[Harlan Grove]

Anat wrote...

The criteria is a text not a number.

....

What's your actual formula?

 

[Guest]

=SUMIF('P&L Cat(YTD May) '!$X$10:$X$152,"P",'P&L Cat(YTD May) '!$C$10:$C$152)

Anat

 

[Guest]

temporarily add a helper column Y in your P&L... sheet
enter in Y10
=if(X10="P",1,"")
copy down to X152
look in column X for "P"s which do not have a 1 next to them. You should be
able to identify what is the difference modify your equation in column Y
until you get a perfect correlation.

 

[Guest]

Thanks I'll try that.

Anat

temporarily add a helper column Y in your P&L... sheet
enter in Y10
=if(X10="P",1,"")
copy down to X152
look in column X for "P"s which do not have a 1 next to them. You should be
able to identify what is the difference modify your equation in column Y
until you get a perfect correlation.