 |
 | 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 / July 2008COUNTIF
I am trying to count just the number of "No Reasons Provided on the Execptions tab and it counts all the reasons fot the vendor. Using the below formula I get 5 instead of the 3 Vendor Registers Reason Barr Nunn Transportation Inc 2111027 No Reason Provided Barr Nunn Transportation Inc 2131663 No Reason Provided Barr Nunn Transportation Inc 2110528 Truck Problems Barr Nunn Transportation Inc 2110529 Dispatch Error Barr Nunn Transportation Inc 2110530 No Reason Provided =COUNTIF(Exceptions!C:C,LEFT(A12,(LEN(A12)-10))) + COUNT(Exceptions!E:E,"No Reason Provided") any help much thanks Like this? =COUNTIF(Exceptions!E:E,"No Reason Provided")  SignatureWigi http://www.wimgielis.be = Excel/VBA, soccer and music > I am trying to count just the number of "No Reasons Provided on the > Execptions tab and it counts all the reasons fot the vendor. [quoted text clipped - 14 lines] > > much thanks no, I need to count on worksheet SERVICE the number of lates for each Vendor, then count just the "No Reason Provided" reasons on worksheet Exceptions for the same Vendor > Like this? > [quoted text clipped - 18 lines] > > > > much thanks I'm not entirely sure what the first part of your formula (the COUNTIF) is doing; but in the second part of the formula, you are using COUNT where I think you should be using COUNTIF. Rick >I am trying to count just the number of "No Reasons Provided on the > Execptions tab and it counts all the reasons fot the vendor. [quoted text clipped - 15 lines] > > much thanks when I use =COUNTIF(Exceptions!C:C,LEFT(A12,(LEN(A12)-10))) + COUNTIF(Exceptions!E:E,"No Reason Provided") it counts all the ,"No Reason Provided" reasons 376 not the 3 indicated below. What I need is just the count for the vendor (A12) Barr Nunn Transportation > I'm not entirely sure what the first part of your formula (the COUNTIF) is > doing; but in the second part of the formula, you are using COUNT where I [quoted text clipped - 21 lines] > > > > much thanks I guess a pivot table would be the easiest option. (In part, this is also due to the fact that I do not fully know your data layout and whay you're trying to measure.) SignatureWigi http://www.wimgielis.be = Excel/VBA, soccer and music > when I use =COUNTIF(Exceptions!C:C,LEFT(A12,(LEN(A12)-10))) + > COUNTIF(Exceptions!E:E,"No Reason Provided") [quoted text clipped - 28 lines] > > > > > > much thanks Use sumproduct =SUMPRODUCT(-(range1=criteria1),--(range2=criteria2)) SignatureRegards, Peo Sjoblom > when I use =COUNTIF(Exceptions!C:C,LEFT(A12,(LEN(A12)-10))) + > COUNTIF(Exceptions!E:E,"No Reason Provided") [quoted text clipped - 29 lines] >> > >> > much thanks If I am reading between the skimpy description of your layout that you have so far provided, I **think** this may be what you are looking for... =SUMPRODUCT((Exceptions!A1:A1000="Barr Nunn Transportation Inc")*(Exceptions!E1:E1000="No Reason Provided")) You will need to adjust the range to cover the maximum rows you ever expect to have in use (you cannot use an entire column reference with the SUMPRODUCT function unless you are using XL2007, hence the need to specify a fixed range)... both ranges in the formula need to be the same length (that is, have the same number of cells referenced). Rick > when I use =COUNTIF(Exceptions!C:C,LEFT(A12,(LEN(A12)-10))) + > COUNTIF(Exceptions!E:E,"No Reason Provided") [quoted text clipped - 29 lines] >> > >> > much thanks close but no cigar ok let me try again 1. there are 2 tabs, Service and Exceptions 2. I am counting on the Service tab the number of lates in column C =COUNTIF(Exceptions!C:C,LEFT(A8,(LEN(A8)-10))) 3. then i would like to count only the "No reasons provided for each vendor COUNT(Exceptions!E:E,"No Reason Provided") 4. when I use this formula I get a total of 5 which is every reason for the Vendor 5. what i would like is 3 for No Reason Provided Vendor Registers Reason Barr Nunn Transportation Inc 2111027 No Reason Provided Barr Nunn Transportation Inc 2131663 No Reason Provided Barr Nunn Transportation Inc 2110528 Truck Problems Barr Nunn Transportation Inc 2110529 Dispatch Error Barr Nunn Transportation Inc 2110530 No Reason Provided > If I am reading between the skimpy description of your layout that you have > so far provided, I **think** this may be what you are looking for... [quoted text clipped - 43 lines] > >> > > >> > much thanks Assuming your reference to the substring in A8 is meaningful, what about this? =SUMPRODUCT((Exceptions!C1:C1000=LEFT(A8,(LEN(A8)-10)))*(Exceptions!E1:E1000="No Reason Provided")) Again, you will need to adjust the range to cover the maximum rows you ever expect to have in use (you cannot use an entire column reference with the SUMPRODUCT function unless you are using XL2007, hence the need to specify a fixed range)... both ranges in the formula need to be the same length (that is, have the same number of cells referenced). Rick > close but no cigar > [quoted text clipped - 71 lines] >> >> > >> >> > much thanks |
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.