COUNTIF

[s2m]

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

 

[Wigi]

Like this?

=COUNTIF(Exceptions!E:E,"No Reason Provided")

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[s2m]

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

 

[Wigi]

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.)

 

[Peo Sjoblom]

Use sumproduct

=SUMPRODUCT(-(range1=criteria1),--(range2=criteria2))





--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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