Search code examples
excelexcel-formulasumifs

Excel - multiple arguments based on SUMIF

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))
Solution

  • This would be the syntactically correct version of your attempt:

    =SUM(SUMIF(Fct_202109082514!A:A,[@Item]&{"","-P","-O"},Fct_202109082514!E:E))