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

any ideas?

Try this approach:

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

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


On Mar 31, 8:04*pm, "Gaurav" wrote:
I need to sum column P if values in column A begin with 000.

any ideas?

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


"Pete_UK" wrote in message
...
Try this approach:

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

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


On Mar 31, 8:04 pm, "Gaurav" wrote:
I need to sum column P if values in column A begin with 000.

any ideas?

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.
--
David Biddulph

"Gaurav" wrote in message
...
Thanks Pete...but i am getting the VALUE errror as result.


"Pete_UK" wrote in message
...
Try this approach:

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

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


On Mar 31, 8:04 pm, "Gaurav" wrote:
I need to sum column P if values in column A begin with 000.

any ideas?

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"),P1:P1000)

Hope this helps.

Pete

On Mar 31, 8:54*pm, "Gaurav" wrote:
Thanks Pete...but i am getting the VALUE errror as result.

"Pete_UK" wrote in message

...
Try this approach:

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

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

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

--
---
HTH

Bob


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



"Gaurav" wrote in message
...
I need to sum column P if values in column A begin with 000.

any ideas?

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.

"Bob Phillips" wrote in message
...
=SUMIF(A:A,"0000*",P:P)

--
---
HTH

Bob


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



"Gaurav" wrote in message
...
I need to sum column P if values in column A begin with 000.

any ideas?

oh I got it

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

Thanks :)

"Bob Phillips" wrote in message
...
=SUMIF(A:A,"0000*",P:P)

--
---
HTH

Bob


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



"Gaurav" wrote in message
...
I need to sum column P if values in column A begin with 000.

any ideas?