XL 2010 - Identify how many records contain BOTH values

[RichardLynch]

Hello,

I have a list of customers and details of which product they had purchased (out of two). Some may have bought two of the same or two different products, or any other combination.
However, I need to identify how many customers have purchased BOTH products.

Attached is an example table. I would need to know how many have purchased both product ABC and product DEF.

Hope that's clear enough for you guys to help.

[PaulM100]

Try this in C2 and down:

Please Login or Register  to view this content.

[RichardLynch]

ignore - message deleted

[RichardLynch]

Try this in C2 and down:

Please Login or Register  to view this content.

Thank you for this however this doesnt work in the way I intended.
Using your formula in my example, it shows as customer 1000001 as having 3 products, however they actually own 2 different types so this should show as 2. I dont need to know if they hold multiple of the same, but only interested in who owns more than one type of product which in this case is ABC and DEF

[Fluff13]

How about, in C2 copied down
=IF(A2=A1,"",SUM(COUNTIFS(A:A,A2,B:B,"ABC")>0,COUNTIFS(A:A,A2,B:B,"DEF")>0))

[PaulM100]

Well, we are not mind readers and if you don't explain properly we will asume what you want. And the formula can be tweeked to make what you want possible as per Fluff's example

[FlameRetired]

I am not so clear either. Here are two interpretations I arrived at.

In C2 and filled down

Please Login or Register  to view this content.

Then in C1

Please Login or Register  to view this content.

	A	B	C
1	Customer Number	Product Purchased	3
2	1000001	ABC	1000001
3	1000001	ABC
4	1000001	DEF
5	1000002	ABC
6	1000003	ABC
7	1000004	ABC
8	1000005	ABC
9	1000006	ABC	1000006
10	1000006	DEF
11	1000007	ABC
12	1000007	ABC
13	1000008	ABC
14	1000009	ABC
15	1000010	ABC	1000010
16	1000010	DEF
17	1000011	ABC
18	1000012	ABC
19	1000013	ABC
20	1000014	ABC
21	1000015	ABC

Another way is to build a table:
Copy - paste all Customer Numbers in G > apply remove duplicates.
Put the Product "names" ABC, DEF in H1:I1.
Then this formula in H2 filled down and across column I

Please Login or Register  to view this content.

Then for the counts in J2 and filled down.

Please Login or Register  to view this content.

	G	H	I	J
1		ABC	DEF	Counts
2	1000001	2	1	2
3	1000002	1	0	1
4	1000003	1	0	1
5	1000004	1	0	1
6	1000005	1	0	1
7	1000006	1	1	2
8	1000007	2	0	1
9	1000008	1	0	1
10	1000009	1	0	1
11	1000010	1	1	2
12	1000011	1	0	1
13	1000012	1	0	1
14	1000013	1	0	1
15	1000014	1	0	1
16	1000015	1	0	1