SUMIFS multiple criteria from same column

[excel_learner]

I have SUMIFS formula where I want to use multiple criteria from the same column apart from other columns.

I have attached the sample file.

Pls assist.

[Admin]

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

[excel_learner]

thanks, but i have date also in the criteria in cell C9.

[Admin]

Hi,

OK.

1.

=SUMPRODUCT(SUMIFS(INDEX($C$2:$E$7,0,MATCH(C9,C1:E 1,0)),$A$2:$A$7,INDEX(A14:B14,0,0),$B$2:$B$7,$C$14 ))

2.

=SUM(SUMIFS(INDEX($C$2:$E$7,0,MATCH(C9,C1:E1,0)),$ A$2:$A$7,{"Micl","Pirt"},$B$2:$B$7,$C$14))

3.

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,A14:B14,0)),--(B2:B7=C14),INDEX($C$2:$E$7,0,MATCH(C9,C1:E1,0)))

HTH

[excel_learner]

Thanks, its working...

[Admin]

Hi,

Glad I could help :cheers:

[MarkJohn]

Good Afternoon - I apologize in advance for responding to this thread, but am new and can't figure out where to post my own thread? I have a similar problem but not sure it's the same solution. How do I attach my workbook?

Thanks in advance.

[Rick Rothstein]

...and can't figure out where to post my own thread? How do I attach my workbook?

First, click the Forum button located under the EXCELFOX.com logo. Next, locate the "Help Section" forum and click the sub-forum link labeled "Excel Help". This will take you into the Excel Help sub-forum where you can read other threads; however, located towards the top is a large button labeled "+Post New Thread"... click that button to take you to a form that you can type your question into. Located below the message panel where you type your question is another panel titled "Additional Options"... inside its panel is a button labeled "Manage Attachments"... click that button and one of its own options allows you to "Upload Files from your Computer".

[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