# Count.ifs

Occasional Contributor

# Count.ifs

Good morning,

I have in one column a list of customers and in another column the products that each customer is buying.

I want to count, for each pair of products, the number of customers that buys them.

If tried to do it using +count.ifs, but it does not work.

What am i missing?

I attach the worksheet I´m using.

Thankyou very much

10 Replies

# Re: Count.ifs

Hi @Jarama11,

Just to make sure I understand your request:

You want see the number of customers who bought product 15 (F2) plus the number of customers who bought product 28 (G2), if so, the following formula will help

=COUNTIFS(\$B\$2:\$B\$446,"="&F2)+COUNTIFS(\$B\$2:\$B\$446,"="&G2)

# Re: Count.ifs

That could be

``=COUNTA(UNIQUE(FILTER(A2:A446,COUNTIF(\$B\$2:\$B\$446,\$F2)*COUNTIF(\$B\$2:\$B\$446,\$G2))))``

# Re: Count.ifs

 Nos of Product Product Total Product Sale Customer Total buy 1 Product 1 32 Customer 50 13 2 Product 10 7 Customer 35 16

find attached sheet

Total Sale = =COUNTIF(B:B,H2)

Total buy by customer = COUNTIF(A:A,J2)

Total Sale =445

# Re: Count.ifs

Thank you Sergei, let me check it, and I will be back to you..

Juan

# Re: Count.ifs

Many thanks, but the point is that I need to count how many customers buy product 1 and Product 2, product 1 and product 3,...

# Re: Count.ifs

Hi,

No, I do not need to count the number of customers that buy product 15 plus the number of customers that buy product 28, but the number of customers that buy product 15 AND product 28 (the pair).

Thanks

Juan

# Re: Count.ifs

Ah,
you want the number of customer that bought both products at the same time!

# Re: Count.ifs

My assumption - number of unique customers. One customer could purchase same pair of products several times, so we need number of customers, not number of purchases.

# Re: Count.ifs

That's right Sergei, unique customers that buy different products, regardless the number of purchases.

Juan

# Re: Count.ifs

Done

check attached sheet

=COUNTIFS(Hoja1!\$A\$2:\$A\$446,\$A2,Hoja1!\$B\$2:\$B\$446,B\$1)