SUMIFS multiple criteria from same column

[Suhail]

Hi,

1.

=SUMPRODUCT(SUMIFS($C$2:$C$7,$A$2:$A$7,INDEX(A14:B 14,0,0),$B$2:$B$7,$C$14))

2.

=SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,{"Micl","Pirt"},$B $2:$B$7,$C$14))

3.

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,A14:B14,0)),--(B2:B7=C14),C2:C7)

HTH

Hi

I also faced same problem but above formulas solved my problem. Thanks. But I have query. While using formula no. 2, I tried to give cell reference instead of placing values in double quotation like {"Micl", "Pirt"} but it didn't work. Please can you tell me how SUM function worked in this formula.

Although formula no. 1 solved my problem and I selected range instead of placing values in curly bracket and double quotations. But I am unable to interpretate how SUMPRODUCT is working here. Please tell me how SUMPRODUCT is working here.

Thanks