How to countif involving another column?

[Bruce Norris]

I'm drawing a blank on how to do this. I need a formula to count "Yes"
values per unique value in another colum. (see example)

In each record...
The first column (A) shows trip codes.
The second column (B) shows either "Yes" or blank.

I need a formula that counts all "Yes" values in column B per trip
code.

For example, using the data below, "2" would be the answer because 2
trip codes corresponded to "Yes".

Trip Covered
tr-24 Yes
tr-24 Yes
tr-24 Yes
tr-50
tr-50
tr-16 Yes
tr-16 Yes
tr-16 Yes
tr-16 Yes

It might help to know when "Yes" appears in a record of a particular trip
code,
it appears in each record of that code. No mixing.

Excel 2002

Thanks for your help!

 

[David McRitchie]

Hi Bruce,
=COUNTIF(B2:B10,"Yes")

as in the other question
http://www.mvps.org/dmcritchie/excel/sumif.htm

Is this homework.

 

[CLR]

If you want a count of "yes's" for each tripcode, take a look at the Data >
Subtotals feature.

Vaya con Dios,
Chuck, CABGx3

 

[Max]

Another way to try

Assuming the table is in cols A and B,
data from row2 down

With C1 left empty
Put in C2:
=IF(AND(COUNTIF($A$2:A2,A2)<2,B2="Yes"),1,"")
Copy C2 down

Then just put in say, D1: =SUM(C:C)
to return the desired result

 

[Biff]

Hi!

So, what you want to do is count unique values that meet a
criteria?

Try this:

=SUMPRODUCT((A1:A9<>"")/COUNTIF(A1:A9,A1:A9&"")*
(B1:B9="yes"))

Biff

 

[Bruce Norris]

Thanks, David, but that would incorrectly produce a count of 7 "Yes",
according to the sample. The correct count is 2. I want a count of how many
trips are "covered" ("Yes"). There are many records bearing the same trip
number. If it is covered, each record bearing that same trip number would
have "Yes" under column B. But the trip number must be counted ONLY once,
not for how many times the number shows up. The trip is one trip that is
covered.

I need more than a simple countif, it seems. I want the "Yes" counted just
for the one particular trip it belongs to. I'll try to lay it out again. In
this example, another cell should show a count of 2 (not 7) covered trips
("Yes")

A B C
Trip Covered Visited
tr-24 Yes Factory
tr-24 Yes Office
tr-24 Yes Warehouse
tr-50 Office
tr-50 Factory
tr-16 Yes HQ
tr-16 Yes Office
tr-16 Yes Plant
tr-16 Yes Warehouse


I know I can manipute the data using subtotaling, adv filter-unique values,
etc. I also know I can concatenate column A and B in another column and then
use an array formula to count only unique values. But I'm hoping to avoid
manipulation, additional procedures, or more columns. I would like to have
one cell with a formula that can do this.

Maybe it's impossible. Do you know of a way to do it?

Thanks for your time.

 

[Bruce Norris]

Thank, Chuck. I'm aware of that feature, but I'm looking of a formula where
the table would not have to be manipulated.

 

[Bruce Norris]

That's an interesting and clever formula. I can use that elsewhere, so
thanks. But here, I need a formula to appear in one cell that counts what I
need.

Thanks.

 

[Bruce Norris]

YES! That's it! Thanks ever so much, Biff. That seems to work great.

I seem to have a tough time comprehending Sumproduct, because it frequently
is used as my solution, but I often need help with it. I must study it more.

Any good tutorials on it?

Thanks again.

 

[Biff]

Hi!

Thanks for the feedback!

Next to IF, SUMPRODUCT may be the most versatile function!

Take a look here for some good stuff:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff

 

[Bruce Norris]

Just skimming the page, it looks very interesting. I can't wait to dig into
it. I've bookmarked it and will delve into it this weekend.

Thanks again for all your help. It's been great.

 

[Harlan Grove]

So, what you want to do is count unique values that meet a
criteria?

Try this:

=SUMPRODUCT((A1:A9<>"")/COUNTIF(A1:A9,A1:A9&"")*(B1:B9="yes"))

....

If there's always a trip code in col A, then this could be reduced to

=SUMPRODUCT((B2:B10="Yes")/COUNTIF(A2:A10,A2:A10))

The &"" only avoids problems when there's a blank in col A with a
corresponding Yes in col B.