Sumif Problems
 

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

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 editPaste special...Values + Multiply.
This should kick Excel into recognising them

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Anat" wrote in message
...
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,

The criteria is a text not a number.

ANat

"Nick Hodge" wrote:

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 editPaste special...Values + Multiply.
This should kick Excel into recognising them

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Anat" wrote in message
...
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

Anat wrote...
The criteria is a text not a number.
....

What's your actual formula?

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

Anat

"Harlan Grove" wrote:

Anat wrote...
The criteria is a text not a number.
....

What's your actual formula?

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.
"Anat" wrote:

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

Anat

"Harlan Grove" wrote:

Anat wrote...
The criteria is a text not a number.
....

What's your actual formula?

Thanks I'll try that.

Anat

"bj" wrote:

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.
"Anat" wrote:

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

Anat

"Harlan Grove" wrote:

Anat wrote...
The criteria is a text not a number.
....

What's your actual formula?