I have a formula that is
=SUM(SUMIF(Fct_202109082514!A:A,{"S100GC-I2C","S100GC-I2C-P","S100GC-I2C-O"},Fct_202109082514!E:E))
And this works, but it's vulnerable to user error while entering the criteria between the quotation marks I am trying to summarize a sheet with a lot of data, and let’s just say that I cannot use Pivot Tables for different reasons.
Anyway, I know I can use this formula to reference a cell for the criteria (instead of typing it “S100GC-I2C”); however, I can only enter one criteria like so:
=SUM(SUMIF(Fct_202109082514!A:A,Table2[@Item],Fct_202109082514!E:E))
Is there a way to have multiple criteria? In essence, I am looking for a formula similar to this, which of course it doesn’t work, but I can't wrap my head around it and how to make it work
=SUM(SUMIF(Fct_202109082514!A:A,Table2[@Item], Table2[@Item]-P, Table2[@Item]-O,Fct_202109082514!E:E))
CodePudding user response：
You can try:
=SUM(Table1[Value]*(Table1[Item]=TRANSPOSE(F2:F4)))
where F2:F4 is your criteria.
In pre-O365 formula should be entered as array formula using Ctrl Shift Enter
CodePudding user response：
Screenshot / 
=SUM((Table2[Sum Range])*(--ISNUMBER(MATCH(Table2[Criteria 1],G5#,0))*ISNUMBER(SEARCH("p",LOWER(Table2[Criteria 2]))*(Table2[Criteria 3]<>""))))
Sum method
=SUM((Table2[Sum Range])*(--ISNUMBER(MATCH(Table2[Criteria 1],G5#,0))ISNUMBER(SEARCH("p",LOWER(Table2[Criteria 2]))(Table2[Criteria 3]<>""))))
he sum -- for the count, very similar function can be used as follows:
count function:
Maybe this training carries forward to other/teens. fe Description
Linked workbook/per screenshot(s) below demonstrate application of a 3 factor criteria model(s)
""Criteria"" a) criteria range includes 1 or more of these volumes b) factors: list within 'double summation' appear reasonable c) non-blank results - check per lady who assumed it mean otherwise
HERE IS THEN LAAST EI
Other
- manual calc using manual adjustments
Notes
- The double sum formula proposed unnecessary - 'inner' sum catch all 'summable' numbers. Result (in context of attached / this exercise of course)
CodePudding user response：
=SUMPRODUCT(--(B4:B6=Table3[Column1])*C4:C6)
Where column B is your criteria list, column C is item quantity and Table3[Column1] is your data list.