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

[TABLE="width: 380"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]product[/TD]
[TD]QTY[/TD]
[TD]customer[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]5[/TD]
[TD]ALEX[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]8[/TD]
[TD]JAMES[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]2[/TD]
[TD]RYAN[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]6[/TD]
[TD]aa[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]9[/TD]
[TD]bb[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]1[/TD]
[TD]dd[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]1[/TD]
[TD]ee[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]1[/TD]
[TD]ff[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]1[/TD]
[TD]gg[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]20[/TD]
[TD]ALEX[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]1[/TD]
[TD]kk[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]1[/TD]
[TD]pp[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]1[/TD]
[TD]UU[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]40[/TD]
[TD]RYAN[/TD]
[/TR]
[TR]
[TD]SR[/TD]
[TD]3[/TD]
[TD]ZZ
[/TD]
[/TR]
</tbody>[/TABLE]

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.