Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count if "yes or no" in one range if there is a unique number in another
Help!!!
I am trying to create a formula on one tab that will count the number of times "yes" or "no" appears in one column based on if a different project number is another column. Formula will only count if the value in column 'a' is a unique number. For example, it will see that A1 and A2 have the same value, so it will look at b2 and b3 only one time and count it as 1. See below for example: Tab "Data Sheet" A B 1 08421 No 2 08421 No 3 06542 Yes 4 06543 Yes Tab "Chart Data" Yes 2 No 1 Last edited by Lorax : April 5th 11 at 04:15 PM |
#2
|
|||
|
|||
Quote:
=IF(COUNTIF($A:$A,A1)=2,"No","Yes")
__________________
Asobi Wa Owari Da |
#3
|
|||
|
|||
Quote:
The formula I need will have to scan through column 'A' and find only unique work order numbers, it will skip duplicates. Once it has found a unique number in column 'A', it will then look at column 'B' for a yes or a no. It will then count the number of times yes or no appears in column 'B'. If a number is repeated in column 'A', it will disregard the duplicates and count one time from column 'B' Column'A' Column 'B W.O. # Complete Pkg 90458179 Yes 90458179 Yes 80053785 Yes 80053785 Yes 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90457063 No 90458387 Yes 90455286 No 90455138 Yes The formula will only look at number, 90457063, once and count one 'No' in column 'B'. It will only count the number of Yes or No's one time regardless of the number of times the number appears in column 'A' The totals shoudl read: Yes = 6 and No = 2 Hope this helps to clarify what I am needing. I am also stuck using Excel 2003.. |
#4
|
|||
|
|||
Dear Lorax, Good afternoon.
I did an example to you. It uses an auxiliar column. It was developed using Excel 2003. It´s he http://www.4shared.com/document/sspz..._Lorax_V1.html
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#5
|
|||
|
|||
Quote:
Assuming column A contains your Work Order Numbers, and column B contains the Yes/No values, in column C, next to the first WO Number, paste in the following formula, then copy and paste it down next to every instance of a WO Number: Code:
=IF(ISERROR(VLOOKUP($A2,$A$1:$A1,1,FALSE)),"Unique","Already Used") Code:
=SUM(IF(B:B="Yes",IF(C:C="Unique",1,0),0)) Code:
{=SUM(IF(B:B="Yes",IF(C:C="Unique",1,0),0))} Last edited by tarquinious : May 3rd 11 at 03:39 PM Reason: forgot something... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can "countifs" be utilized to return a count of unique values? | Excel Worksheet Functions | |||
Count letter"B" in one column based on unique value among duplicat | Excel Worksheet Functions | |||
Count the number of cells that conatin a "." in a range | Excel Worksheet Functions | |||
Formula to count number of time stamps within a range in a column having dates formatted as "custom" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |