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
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?
> > >
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks