Count.ifs

%3CLINGO-SUB%20id%3D%22lingo-sub-1947682%22%20slang%3D%22en-US%22%3ECount.ifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947682%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3EI%20have%20in%20one%20column%20a%20list%20of%20customers%20and%20in%20another%20column%20the%20products%20that%20each%20customer%20is%20buying.%3C%2FP%3E%3CP%3EI%20want%20to%20count%2C%20for%20each%20pair%20of%20products%2C%20the%20number%20of%20customers%20that%20buys%20them.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20tried%20to%20do%20it%20using%20%2Bcount.ifs%2C%20but%20it%20does%20not%20work.%3C%2FP%3E%3CP%3EWhat%20am%20i%20missing%3F%3C%2FP%3E%3CP%3EI%20attach%20the%20worksheet%20I%C2%B4m%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThankyou%20very%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1947682%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948173%22%20slang%3D%22en-US%22%3ERe%3A%20Count.ifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948173%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888182%22%20target%3D%22_blank%22%3E%40Jarama11%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20make%20sure%20I%20understand%20your%20request%3A%3C%2FP%3E%3CP%3EYou%20want%20see%20the%20number%20of%20customers%20who%20bought%20product%2015%20(F2)%20plus%20the%20number%20of%20customers%20who%20bought%20product%2028%20(G2)%2C%20if%20so%2C%20the%20following%20formula%20will%20help%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24B%242%3A%24B%24446%2C%22%3D%22%26amp%3BF2)%2BCOUNTIFS(%24B%242%3A%24B%24446%2C%22%3D%22%26amp%3BG2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948179%22%20slang%3D%22en-US%22%3ERe%3A%20Count.ifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888182%22%20target%3D%22_blank%22%3E%40Jarama11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTA(UNIQUE(FILTER(A2%3AA446%2CCOUNTIF(%24B%242%3A%24B%24446%2C%24F2)*COUNTIF(%24B%242%3A%24B%24446%2C%24G2))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948377%22%20slang%3D%22en-US%22%3ERe%3A%20Count.ifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888182%22%20target%3D%22_blank%22%3E%40Jarama11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22403%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2270%22%3ENos%20of%20Product%3C%2FTD%3E%3CTD%20width%3D%2280%22%3EProduct%3C%2FTD%3E%3CTD%20width%3D%2293%22%3ETotal%20Product%20Sale%3C%2FTD%3E%3CTD%20width%3D%2280%22%3ECustomer%3C%2FTD%3E%3CTD%20width%3D%2280%22%3ETotal%20buy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EProduct%201%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3ECustomer%2050%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EProduct%2010%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3ECustomer%2035%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efind%20attached%20sheet%3C%2FP%3E%3CP%3ETotal%20Sale%20%3D%26nbsp%3B%3DCOUNTIF(B%3AB%2CH2)%3C%2FP%3E%3CP%3ETotal%20buy%20by%20customer%20%3D%26nbsp%3BCOUNTIF(A%3AA%2CJ2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETotal%20Sale%20%3D445%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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)

@Jarama11 

That could be

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

@Jarama11 

Nos of ProductProductTotal Product SaleCustomerTotal buy
1Product 132Customer 5013
2Product 107Customer 3516

 

find attached sheet

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

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

 

Total Sale =445

 

 

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


Juan
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,...

@Jihad Al-Jarady 

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

Hi @Jarama11 

 

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

@Jihad Al-Jarady 

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.

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

Juan

@Jarama11 

 

Done

check attached sheet

 

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