multiple If and SUMIFS

Question

Good evening,Need help with following multiple criteria. Trying to select Department or all expenses through drop down menu. Thank youNo problem with one criteria (first statement of selecting Marketing), getting error when adding second criteria&nbsp;&nbsp; #1)=IF($B$3="Marketing",IF(D$4="Actuals",-SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$E:$E,'Departmental Income Statment'!$B$3,'Netsuite IncomeStatement'!$B:$B,'Departmental Income Statment'!$B45,,#2) IF(B3="Actuals v Plan FY26, 'Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!B:B,'Departmental Income Statment'!B45,0""))&nbsp;

nikolinode · Answer

You want:If B3 = "Marketing", run a SUMIFS with Department = MarketingIf B3 = "Actuals v Plan FY26", run a different SUMIFSOtherwise return 0=IF(&nbsp;&nbsp;&nbsp;&nbsp;$B$3="Marketing",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Netsuite IncomeStatement'!$E:$E,$B$3,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp;IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $B$3="Actuals v Plan FY26",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),0))&nbsp;Make sure spellings match exactly"Actuals v Plan FY26" must be identical to the dropdown text.Confirm correct sheet nameYou have a typo:Departmental Income Statment → missing e in "Statement"Fix the sheet name if needed.&nbsp;Hope this helps you.

arali · Answer

Thanks Niko.&nbsp;

olufemi7 · Answer

HiARali​&nbsp;The error you’re seeing comes from the way the second `IF` was written, &nbsp;it’s missing proper parentheses and quotation marks, and the `SUMIFS` arguments weren’t closed correctly. Each `IF` must always have three parts:• Condition• Value if true• Value if falseHere’s a corrected version of your formula that handles both dropdowns (Department in `$B$3` and Report Type in `D$4`):=IF($B$3="Marketing",&nbsp; &nbsp; IF(D$4="Actuals",&nbsp; &nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Netsuite IncomeStatement'!$E:$E,$B$3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp; &nbsp; &nbsp; IF(D$4="Actuals v Plan FY26",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; IF(D$4="Actuals",&nbsp; &nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp; &nbsp; &nbsp; IF(D$4="Actuals v Plan FY26",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))&nbsp;How it works• `$B$3` → Department dropdown (e.g., “Marketing” or “All”).• `D$4` → Report type dropdown (“Actuals” or “Actuals v Plan FY26”).• `SUMIFS` applies filters:• Column `E:E` for Department.• Column `B:B` for Account/line item (`$B45`).• The negative sign (`-SUMIFS`) flips values if needed.• If neither condition matches, the formula returns `0`.TipIf you’re on Excel 2016 or later, you can simplify the nesting by using the `IFS` function:=IFS(&nbsp; &nbsp;AND($B$3="Marketing",D$4="Actuals"),&nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$E:$E,$B$3,'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp;AND($B$3="Marketing",D$4="Actuals v Plan FY26"),&nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp;AND($B$3&lt;&gt;"Marketing",D$4="Actuals"),&nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$B:$B,$B45),&nbsp; &nbsp;AND($B$3&lt;&gt;"Marketing",D$4="Actuals v Plan FY26"),&nbsp; &nbsp; &nbsp; -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$B:$B,$B45))In short: fix the missing parentheses/commas, and structure each `IF` with a clear true/false branch. That should resolve the error you’re seeing.

Forum Discussion

multiple If and SUMIFS

3 Replies