Sum ifs 3 criteria

Karrie Davis

I'm looking for the sumifs= Status column is "green", The Trans Date column in January 2018 the reversed column is unchecked.

 

So far I only have the first part of the formula working. I cannot figure out how to include the rest

=SUMIF(@{Collections Range 3}, @cell = "Green", {Collections Range 1}) This information will be referenced in another sheet.

Nic Larsen

Are you summing the "amount" column based on the 3 criteria? If so, this format should work just update the column references:

=SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], IFERROR(MONTH(@cell), 0) = 1)

 

Karrie Davis

The formula worked, I added another criteria however it calculated everything not just January

 

=SUMIFS({Collections Range 1}, {Collections Range 2}, ="Receipt", {Collections Range 3}, ="Green", {Collections Range 4}, =0, {Collections Range 5}, IFERROR(MONTH(@cell), 0) = 1)

Karrie Davis

Actually it does give me January just not 2018, I have multiple years on a the original chart how can I specifiy to just one year

 

Nic Larsen

Here is mine with the option of a specific year. 

=SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], IFERROR(MONTH(@cell), 0) = 1, [Tran Date]:[Tran Date], IFERROR(YEAR(@cell), 0) = 2019)

Paul Newcome

You can also use an OR or an AND statement if you are referencing the same range twice. I personally find it helps me keep track of what I have and have not accounted for yet, keeps things a little more organized, and helps keep the overall length of the formula down a touch.

 

=SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2019))

.

You can also use cell references for the month and year criteria to save yourself from having to go in and change the formula with each month/year combo. I see you are using a different sheet for your calculations, so you could add a helper column that has separate cells for the month and year entry (for this example I'll call it [Date Helper]).

=SUMIFS(Amount:Amount, Status:Status, ="Green", Reversed:Reversed, =0, [Tran Date]:[Tran Date], AND(IFERROR(MONTH(@cell), 0) = [Date Helper]1, IFERROR(YEAR(@cell), 0) = [Date Helper]2))

.

You can also use cell references to auto-update when dragfilling if you are tracking multiple month/year combos in a grid type layout.