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.
"David McRitchie" wrote in message
...
Hi Bruce,
=COUNTIF(B2:B10,"Yes")
as in the other question
http://www.mvps.org/dmcritchie/excel/sumif.htm
Is this homework.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Bruce Norris" wrote in message
. ..
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!