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.
Comments
-
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)
-
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)
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!