different SUMIF

[Gaurav]

I need to sum column P if values in column A begin with 000.

any ideas?

 

[Pete_UK]

Try this approach:

=SUMPRODUCT((LEFT(A1:A1000,3)="000")*(P11000))

I've assumed 1000 rows, so adjust to suit, but you can't use full
column references prior to XL 2007.

Hope this helps.

Pete

 

[Bob Phillips]

=SUMIF(A:A,"0000*",P)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gaurav]

thanks bob this worked. another one...now I want to sum the rest of the
values. means....sum column P where values in column A dont start with 000.

 

[Gaurav]

Thanks Pete...but i am getting the VALUE errror as result.


Try this approach:

=SUMPRODUCT((LEFT(A1:A1000,3)="000")*(P11000))

I've assumed 1000 rows, so adjust to suit, but you can't use full
column references prior to XL 2007.

Hope this helps.

Pete

 

[Gaurav]

oh I got it

=SUMIF(A:A,"<>000*",P)

Thanks

=SUMIF(A:A,"0000*",P)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[David Biddulph]

So you need to look at the values in column P. Get rid of text entries or
errors.

Note also that the column A condition will return a true condition if you
have text values in that column starting with 000, but not if you have
numbers such as 123 formatted 000000 to look like 000123.

 

[Pete_UK]

I see you have an answer from Bob that works for you, but you might
like to try this amendment:

=SUMPRODUCT(--(LEFT(A1:A1000,3)="000"),P11000)

Hope this helps.

Pete