Counting Unique Values

[handsdown]

How would I go about calculating the unique values in Column A based on the criteria that Column B is not blank (any text at all)? In other words, in this example, the formula should output 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated. Thank you in advance!

 

[AhoyNC]

If there was text in column B for the 2nd 2 would the answer still be 2 or 3.

Would the answer be 2 or 3 for the below?

Excel Workbook
	A	B
1	Column A	Column B
2	1	No
3	2	Yes
4	2	No
5	3
Sheet

Would the answer be 2 or 3 for the example below?

Excel Workbook
	A	B
8	Column A	Column B
9	1	No
10	2	Yes
11	2	Yes
12	3
Sheet

 

[handsdown]

Both would be 2.

 

[AhoyNC]

Try one of these array formulas.
The formula in cell D2 will work if the values in column A are text and/or numeric.
The formula in D4 is a little shorter, but will only work if column A is numeric.

Formulas need to be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Column A	Column B		Count
2	1	No		2
3	2	Yes
4	2			2
5	3
Sheet

 

[handsdown]

Still isn't working. I have to do the entire column of A and B, as I am not sure how long this document may be.

I slightly modified it to become =SUM(IF(FREQUENCY(IF(B:B<>"",A:A),A:A),1)). However, I still run into the same problem I have been having. It counts the unique numbers, but doesn't recognize that Column B isn't filled on some rows.

For example, it says this is 3, when I want it to return 2.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for working with me and the help!

 

[AhoyNC]

See below it's working for me.
To use this formula column A must be numeric.
Also, did you enter the formula with CTRL-SHIFT-ENTER. If done right Excel will put brackets {} around the formula.
I you don't see the brackets, put the cursor in the cell with the formula then press the F2 (for edit) then CTRL-SHIFT-ENTER (on a PC).

Excel Workbook
	A	B	C	D
1	Column A	Column B		Count
2	1	No
3	2	Yes		4
4	2
5	3
6	3
7	4	Yes
8	4	Yes
9	5
10	5
11	5	No
Sheet

 

[handsdown]

I got it to work! I had some of my INDIRECT references wrong. Thank you so much for your help!