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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Anat" <[email protected]> wrote in message
news:[email protected]...
>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 edit>Paste special...>Values + Multiply.
> This should kick Excel into recognising them
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Anat" <[email protected]> wrote in message
> news:[email protected]...
> >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?
> > >
> > >