help with formula ( count and suim f)

[Imran Azam]

hi guys

a i am trying to count the number of customers that have both product AR and SR
and what is the combined QTY

so for example

i want something like this

Product combo combined qty number of customer
AR&SR 67 2

below is the data how can i do this ? i cant get the sumif and count if correct

product	QTY	customer
AR	5	ALEX
AR	8	JAMES
AR	2	RYAN
AR	6	aa
AR	9	bb
AR	1	dd
AR	1	ee
AR	1	ff
AR	1	gg
SR	20	ALEX
SR	1	kk
SR	1	pp
SR	1	UU
SR	40	RYAN
SR	3	ZZ

<colgroup><col><col><col></colgroup><tbody>
</tbody>

thanks for any helo

 

[Imran Azam]

anyone?

 

[VBABEGINER]

Product combo combined qty number of customer
AR&SR 67 2

How 67 comes ? and 2 ?

-----

If you make a sum of AR & SR then Total = 100

 

[tyija1995]

Assuming you have the product in alphabetical order I.e. AR ALWAYS comes before SR, and that a customer is only listed once per product then try this:
Add 2 helper columns; (Assuming your data is in A2:C16) then:

Set D2 =SUMPRODUCT(--($C$2:$C$16=C2)) and drag formula down to D16

Set E2 =IFNA(IF(D2=1,"",B2+INDEX(A3:$D$16,MATCH(C2,C3:$C$16,FALSE),2)),"") and drag formula down to E16

Then in cell F2 add the formula for final answer:
=CONCAT("AR&SR ",SUM(E2:E16)," ",COUNT(E2:E16))

In this example I have F2 = "AR&SR 67 2" which is what you have put in your initial post.

EDIT: Changed F2 formula to make more sense than before.