 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008different SUMIF
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 > 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. 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" <gsha...@mediconnect.net> 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 > Thanks Pete...but i am getting the VALUE errror as result. > [quoted text clipped - 13 lines] >> >> 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 > Thanks Pete...but i am getting the VALUE errror as result. > [quoted text clipped - 8 lines] > > Pete =SUMIF(A:A,"0000*",P:P)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >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. > =SUMIF(A:A,"0000*",P:P) > >>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 :) > =SUMIF(A:A,"0000*",P:P) > >>I need to sum column P if values in column A begin with 000. >> >> any ideas?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.