Sum ifs 3 criteria

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.

if formula.PNG

Find more posts tagged with

Formulas

Legal

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Contact List showing as a text only
It happened suddenly. It was working last week. I'm using Index Collect to pull a contact as cross sheet reference. But today, I did not see that working well. Usually it has circle beside my name, suddenly it vanishes, and it just contains text. I'm using those other columns to be sent notification, and it doesn't work…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…