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!

In D1, enter:

=A1 & C1 and copy down

and then count uniques in column D:

http://www.cpearson.com/excel/Duplicates.aspx
--
Gary''s Student - gsnu200792


"Makaron" wrote:

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Makaron" wrote:
> 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!

Errata, line below should have read as:
> Simply, place in G2, copy down: =SUMIF(C:C,F2,D)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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)

--
Biff
Microsoft Excel MVP


"Makaron" <(E-Mail Removed)> wrote in message
news:C1BDD9BD-18A5-4C3F-ACA8-(E-Mail Removed)...
>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!

Thank you very much - great job!

"Max" wrote:

> Errata, line below should have read as:
> > Simply, place in G2, copy down: =SUMIF(C:C,F2,D)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>

welcome, glad it worked for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Makaron" <(E-Mail Removed)> wrote in message
news:B790CBD8-7CD0-4F20-A979-(E-Mail Removed)...
> Thank you very much - great job!