CODEWITHSUNDEEP
excel-formula
Dave_B
Dave_B

Reputation: 21

Excel Sumifs with Multiple OR Criteria

I'm trying to build a Sumifs formula that uses multiple OR criteria, but also uses cell references in the criteria. I've found on other posts how to use an array constant (ie. Sumifs('Sum Range', 'Criteria Range', {value 1, value 2})) to achieve this, but array constants don't allow for cell references; only static-defined values.

Here's a simplified example my data: https://i.sstatic.net/OdfCN.png

I need to build a Sumifs formula in cell 'TABLE B'!C2 which returns the sum of Price values from column 'TABLE A'!C:C where:

('Table A'!A:A = 'TABLE B'!A:A or "ALL") AND ('Table A'!B:B = 'TABLE B'!B:B or "ALL")

This formula should return a value in 'TABLE B'!C2 of: 

'TABLE A'!C3 +'TABLE A'!C4 + 'TABLE A'!C5  
= $9.00

Thanks!

Upvotes: 2

Views: 498

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You would use this:

=SUMPRODUCT((('Table A'!$A$2:$A$5='TABLE B'!A2)+('Table A'!$A$2:$A$5="ALL"))*(('Table A'!$B$2:$B$5='TABLE B'!B2)+('Table A'!$B$2:$B$5="All")),'Table A'!$C$2:$C$5)

Upvotes: 1

Related Questions