Forum Discussion

ARali's avatar
ARali
Copper Contributor
Nov 26, 2025

multiple If and SUMIFS

Good evening, Need help with following multiple criteria. Trying to select Department or all expenses through drop down menu. Thank you No problem with one criteria (first statement of selecting Ma...
excel
Olufemi7's avatar
Olufemi7
Iron Contributor
Dec 08, 2025

HiARali​ 

The error you’re seeing comes from the way the second `IF` was written,  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 false


Here’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",
    IF(D$4="Actuals",
        -SUMIFS('Netsuite IncomeStatement'!AD:AD,
                'Netsuite IncomeStatement'!$E:$E,$B$3,
                'Netsuite IncomeStatement'!$B:$B,$B45),
        IF(D$4="Actuals v Plan FY26",
            -SUMIFS('Netsuite IncomeStatement'!AD:AD,
                    'Netsuite IncomeStatement'!$B:$B,$B45),
            0
        )
    ),
    IF(D$4="Actuals",
        -SUMIFS('Netsuite IncomeStatement'!AD:AD,
                'Netsuite IncomeStatement'!$B:$B,$B45),
        IF(D$4="Actuals v Plan FY26",
            -SUMIFS('Netsuite IncomeStatement'!AD:AD,
                    'Netsuite IncomeStatement'!$B:$B,$B45),
            0
        )
    )
)

 

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`.

Tip

If you’re on Excel 2016 or later, you can simplify the nesting by using the `IFS` function:

=IFS(
   AND($B$3="Marketing",D$4="Actuals"),
      -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$E:$E,$B$3,'Netsuite IncomeStatement'!$B:$B,$B45),
   AND($B$3="Marketing",D$4="Actuals v Plan FY26"),
      -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$B:$B,$B45),
   AND($B$3<>"Marketing",D$4="Actuals"),
      -SUMIFS('Netsuite IncomeStatement'!AD:AD,'Netsuite IncomeStatement'!$B:$B,$B45),
   AND($B$3<>"Marketing",D$4="Actuals v Plan FY26"),
      -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.