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

Need help with an Index/Match using TEXT for Dates
Can someone help me understand how I can correct this formula? It's returning Unparsable. =INDEX({Revenue & Opex | ARRONI Row}, MATCH(TEXT(DATE(YEAR({Revenue & Opex Go Live Date}), MONTH({Revenue & Opex Go Live Date}) + 11, 1), "MM/DD/YYYY"), {Revenue & Opex Date Header}, 0)) The formula is indexing a full row. Matching a…
Help for INDEX/COLLECT with multiple criteria
Hi everyone! I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing… I have an "Audience" sheet my team is using with employees' respective name, ID…
How do I limit the number of entries under the same company name
I have a list of companies that are allowed 2 complimentary registrations. I want a notification sent if a third registration is added that tell the company rep they need to pay for the third registration. I have a list of company names with the contact information. I then have the registration list with a form for…