Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Excel countif based on size of numbers in one column in Access | Excel Worksheet Functions | |||
Referencing previous column in COUNTIF | Excel Discussion (Misc queries) | |||
How do you use countif for several column criteria? | Excel Worksheet Functions |