Count unique values in columns based on 2 criteria (error in one result)

**t83357** · 08-12-2013, 05:46 PM

I am trying to calculate the following values. There is one formula that is not returning the right value. Cell B3 should be returning a value of 3 and it is returning a value of 5.

Here is the formula:
=SUM(IF(FREQUENCY(IF((A9:A29<>"")*(B9:B29=""), MATCH(A9:A29,A9:A29,0)),ROW(A9:A29)-MIN(ROW(A9:A29))+1),1))

The spreadsheet is attached.

**XOR LX** · 08-12-2013, 05:50 PM

Why?

I see five e-mail addresses in that range with a blank in the phone column, all of which are unique:

bbbbb, cccccc, eeeee, ffffffff and gggggg

Regards

**t83357** · 08-12-2013, 06:00 PM

I can't deny I have been looking at this too long so let me clarify:

I am looking to return a value of 3 since these are the only email addresses with no phone number associated with them in any row.
bbbbb
cccccc
ffffffff

These two do have phone numbers associated with them on other rows.
row 14 eeeee
row 21 ggggg

**icestationzbra** · 08-16-2013, 10:12 AM

this was fun.

create Named Ranges (ALT > M > N) for your email list and phone list (eml and phn in my case). then use the following ARRAY formula.

Please Login or Register  to view this content.

UPDATE:

inspired by Tony Valko's idea from below, here is a far simpler ARRAY formula:

Please Login or Register  to view this content.

**Tony Valko** · 08-16-2013, 04:54 PM

This one has fewer "moving parts".

Array entered**:

=SUM(IF(FREQUENCY(IF(COUNTIFS(A9:A29,A9:A29,B9:B29,"<>")=0,IF(A9:A29<>"",MATCH(A9:A29,A9:A29,0))),ROW(A9:A29)-ROW(A9)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.