ExcelBanter - Count if "yes or no" in one range if there is a unique number in another

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count if "yes or no" in one range if there is a unique number in another (https://www.excelbanter.com/excel-worksheet-functions/270397-count-if-yes-no-one-range-if-there-unique-number-another.html)

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

Quote:

Originally Posted by Lorax (Post 964859)

For each cell in column B, the formula should contain:
=IF(COUNTIF($A:$A,A1)=2,"No","Yes")

Quote:

Originally Posted by wickedchew (Post 964863)

For each cell in column B, the formula should contain:
=IF(COUNTIF($A:$A,A1)=2,"No","Yes")

Alas, it did not perform as needed. I believe I might need to explain more in detail.

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..

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

Quote:

Originally Posted by Lorax (Post 964869)

I have answered a similar post elsewhere where the request was for a formula to display whether a value was Unique or Already Used. Below is this formula, then taking it one step further, the formula required to count the number of times a Yes appears next to a Unique WO Number.

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

Now, in a cell where you want the result of how many Yes values appear next to a Unique Work Order Number, paste in the formula:

Code:

=SUM(IF(B:B="Yes",IF(C:C="Unique",1,0),0))

Finally, on that cell where you just pasted the SUM formula above, click to put the cursor in the formular bar as if you were editing the formula, then press CTRL+SHIFT+ENTER. This will put curly braces {} around the formula so that it looks like this:

Code:

{=SUM(IF(B:B="Yes",IF(C:C="Unique",1,0),0))}

For the sample data you gave above, this gave me the result of 4.