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

[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=A 2)*(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!

[Max]

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

[T. Valko]

Try this array formula** :

=COUNT(1/FREQUENCY(IF((A2:A13<"")*(C2:C13<""),MATCH(A2:A1 3&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" wrote in message
...
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!

[Makaron]

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:D)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

welcome, glad it worked for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Makaron" wrote in message
...
Thank you very much - great job!