SUMIFS multiple criteria from same column

Printable View

07-23-2011, 04:41 PM
excel_learner

1 Attachment(s)

SUMIFS multiple criteria from same column

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.
07-23-2011, 05:05 PM
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
07-23-2011, 05:15 PM
excel_learner

thanks, but i have date also in the criteria in cell C9.
07-23-2011, 05:23 PM
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
07-23-2011, 05:35 PM
excel_learner

Thanks, its working...
07-23-2011, 05:40 PM
Admin

Hi,

Glad I could help :cheers:
05-17-2012, 11:41 PM
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.
05-17-2012, 11:53 PM
Rick Rothstein

Quote:

Originally Posted by MarkJohn

...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".
06-30-2012, 01:56 PM
Suhail

SUMIFS Multiple Criteria in Single Cloumn

Quote:

Originally Posted by 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

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