|
COUNTIF
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 |
COUNTIF
Like this?
=COUNTIF(Exceptions!E:E,"No Reason Provided") -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "s2m" wrote: 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 |
COUNTIF
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 "s2m" wrote in message ... 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 |
COUNTIF
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 "Wigi" wrote: Like this? =COUNTIF(Exceptions!E:E,"No Reason Provided") -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "s2m" wrote: 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 |
COUNTIF
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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
COUNTIF
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.) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "s2m" wrote: 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
COUNTIF
Use sumproduct =SUMPRODUCT(-(range1=criteria1),--(range2=criteria2)) -- Regards, Peo Sjoblom "s2m" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
COUNTIF
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 "s2m" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
COUNTIF
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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
COUNTIF
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 "s2m" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: 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 "s2m" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com