Counting Unique Pairs of Cells

[Makaron]

I am trying to count unique pairs of cells in different columns. For
instance, I have:

"A" "C"
1 apples
2 oranges
2 oranges
2 oranges
3 bananas
4 oranges
4 oranges
5 apples
oranges
6
7 apples
7 apples

I am trying to count the total number of unique pairs of cells that have
particular (non-blank) entry in the second column AND a nonzero corresponding
value in the first (for example, for "oranges" I would like to end up with a
count of 2; for "apples" - with 3, and a total count of 6)

Thank you very much in advance - I appreciate it!

 

[Gary''s Student]

In D1, enter:

=A1 & C1 and copy down

and then count uniques in column D:

http://www.cpearson.com/excel/Duplicates.aspx

 

[Max]

Another straightforward play which gets you there ..

Assume data as posted in cols A and C, from row2 down

In D2, copied down:
=IF(SUMPRODUCT((A$2:A2<>"")*(C$2:C2<>"")*(A$2:A2=A2)*(C$2:C2=C2))>1,"",SUMPRODUCT((A$2:A2<>"")*(C$2:C2<>"")*(A$2:A2=A2)*(C$2:C2=C2)))

Then, assuming you have listed the unique fruits:
apples, oranges, bananas
in F2:F4

Simply, place in G2, copy down: =SUMIF(B:B,E2,C:C)
will return the required figs for each fruit

 

[Max]

Errata, line below should have read as:

Simply, place in G2, copy down: =SUMIF(C:C,F2,D)

---

 

[T. Valko]

Try this array formula** :

=COUNT(1/FREQUENCY(IF((A2:A13<>"")*(C2:C13<>""),MATCH(A2:A13&C2:C13,A2:A13&C2:C13,0)),ROW(A2:A13)-MIN(ROW(A2:A13))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Makaron]

Thank you very much - great job!

Errata, line below should have read as:

 

[Max]

welcome, glad it worked for you