SUMIFS with multiple criteria with one at row level

WillieBNZ

Hi there,

Going bonkers trying to work this out...I am sure a simple mistake somewhere, maybe tied to @cell or @row....

I am trying to sum the total of three possible stipends paid (Stipend 1, Stipend 2, Exam Stipend) in a specific period however only if the person was part of a specific program. Entering the below doesn't throw an error however it's not filtering on "program applied to". My guess is it's looking at the entire "program applied to" column, seeing the criteria, and then including, versus only summing if the "program applied to" criteria is on that row.

Thx,

Willie

=SUMIFS([Stipend 1 Amount]:[Stipend 1 Amount], [Stipend 1 Paid Date]:[Stipend 1 Paid Date], <DATE(2022, 1, 1), [Stipend 1 Paid Date]:[Stipend 1 Paid Date], >=DATE(2021, 1, 1)) + SUMIFS([Stipend 2 Amount]:[Stipend 2 Amount], [Stipend 2 Paid Date]:[Stipend 2 Paid Date], <DATE(2022, 1, 1), [Stipend 2 Paid Date]:[Stipend 2 Paid Date], >=DATE(2021, 1, 1)) + SUMIFS([Exam Stipend Amount]:[Exam Stipend Amount], [Exam Stipend Paid Date]:[Exam Stipend Paid Date], <DATE(2022, 1, 1), [Exam Stipend Paid Date]:[Exam Stipend Paid Date], >=DATE(2021, 1, 1), [Program Applied to]:[Program Applied to], "Horizon")

Dale Murphy

@WillieBNZ You are aware that the "program applied to" condition is only in one of the SUMIFS statements? Just making sure that is intentional ...

dm

Dale Murphy

@WillieBNZ You are aware that the "program applied to" condition is only in one of the SUMIFS statements? Just making sure that is intentional ...

dm

Summer

Hi @WillieBNZ Hopefully someone will come along and give you an exact answer.

In the mean time, have you considered starting your formula with the main criteria of Program Applied to being Horizon? Basically writing the formula so it follows the logic of

Look at the Program Applied to Column, if the program is "Horizon" than SUMIFS.....

If the program is not "Horizon" than 0 or ""

As an aside, I find that helper columns specifically for dates are a great way to be able to update the formula without manually typing in the date. You could place the 2021, 01, 01 date in the sheet summary or in another tucked away cell on your sheet and update the formula to point to that reference. Doing this would also allow you to change your criteria and search different dates.

WillieBNZ

@Dale Murphy Yeah, well kind of....but then confused myself, so started thinking along the lines of what @Summer is saying and have the initial criteria up front before the SUMIFS. I just added the condition to all 3 SUMIFs statement and it works (so thanks), but interested how to do the initial filter before the SUMIFS as much simpler. I get the feeling a few of my formulas are more complex than needed.

And thanks @Summer for the date tip. It was pretty painful writing it all out and not dropping a typo.

Willie

Summer

@WillieBNZ . I didn't mean to add confusion. I am glad Dale's comment pointed you in the right direction and your revised formulas are now working. Mark his response as the answer if you're so inclined.

Side note: If you make a key for your "Program Applied To" and point your formulas to a specific cell, like' you're doing for the date, you can easily adapt your formula to calculate for any of the programs in your keys.

WillieBNZ

Thx.