Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bruce Norris
 
Posts: n/a
Default How to countif involving another column?

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!


  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

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!





  #3   Report Post  
CLR
 
Posts: n/a
Default

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

Vaya con Dios,
Chuck, CABGx3


"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!




  #4   Report Post  
Max
 
Posts: n/a
Default

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"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!




  #5   Report Post  
Biff
 
Posts: n/a
Default

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

-----Original 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!


.



  #6   Report Post  
Bruce Norris
 
Posts: n/a
Default

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!







  #7   Report Post  
Bruce Norris
 
Posts: n/a
Default

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


"CLR" wrote in message
...
If you want a count of "yes's" for each tripcode, take a look at the Data

Subtotals feature.

Vaya con Dios,
Chuck, CABGx3


"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!






  #8   Report Post  
Bruce Norris
 
Posts: n/a
Default

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.

"Max" wrote in message
...
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"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!






  #9   Report Post  
Bruce Norris
 
Posts: n/a
Default

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" wrote in message
...
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

-----Original 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!


.



  #10   Report Post  
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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" wrote in message
...
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

-----Original 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!


.



.



  #11   Report Post  
Bruce Norris
 
Posts: n/a
Default

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.

"Biff" wrote in message
...
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

-----Original Message-----
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" wrote in message
...
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

-----Original 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!


.



.



  #12   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Biff" wrote...
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort by row instead of by column? PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM
Combining IF and COUNTIF based on two columns maxtrixx Excel Discussion (Misc queries) 5 March 31st 05 06:21 PM
Excel countif based on size of numbers in one column in Access Kristjan_Thor Excel Worksheet Functions 3 March 17th 05 11:21 PM
Referencing previous column in COUNTIF [email protected] Excel Discussion (Misc queries) 6 March 1st 05 11:27 AM
How do you use countif for several column criteria? Fustrated Excel Worksheet Functions 1 February 9th 05 06:30 PM


All times are GMT +1. The time now is 09:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"