SUMIFS Formula unparsable
Hello,
My formula below keeps coming back unparsable and I can't figure out why...The column I need to be summed is referenced first in Range 2. I am also referencing the same date column for that Month and Year criterion. It's on the tip of my tongue but I am running out of options. Any help here would be wonderful. I need range 2 values to be summed when AUTO is selected and the date lands anytime in January 2019.
=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 1}, "AUTO", {Peterson Family Transactions Range 3}, MONTH(@CELL) = 1, {Peterson Family Transactions Range 4}, YEAR(@CELL) = 2019)
Thanks for your help in advance:)
Comments
-
If you are referencing the same date column for the month and the year, it should be the same range (either 3 or 4). I would start by verifying that. One thing I have found that helps avoid that is by using the AND or OR functions when I am referencing the same range for multiple sets of criteria. I also rename my ranges as "Sheet Name - Column Name" to make for easier referencing when you do run into a problem.
Double check your date column ranges first, and if you would like (summary of all this mess is at the bottom)...
=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 1}, "AUTO", {Peterson Family Transactions Range 3}, AND(MONTH(@CELL) = 1, YEAR(@cell) = 2019))
.
I have also found it useful to use cell references in my formulas as opposed to hard data. If I only need to look at one month at a time, I would use two separate cells elsewhere on the sheet that I can change the month and year in without having to edit the formula itself.
=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, "AUTO", {Peterson Family Transactions - Date}, AND(MONTH(@cell) = [Help Column]$1, YEAR(@cell) = [Help Column]$2))
If I am doing a comparison or year to date tracking, I will build out a table. The specific requirements would determine the actual layout, but there would be a column or row where the months are listed out. I would then use a helper column or row and put the month numbers there and then reference that column or row in my formula. So assuming my months are going down the Month column and my month numbers are in a hidden column titled [Month Number], and I used row 1 of the Month column to input my date, the table would look like this...
Month Count Month Number (hidden)
2019
Jan F 1
Feb F 2
Mar F 3
and my formula would be something along the lines of
=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, "AUTO", {Peterson Family Transactions - Date}, AND(MONTH(@cell) = $[Month Number]@row, YEAR(@cell) = $Month$1))
.
For multiple types...
Type Jan Feb Mar Apr
2019 1 2 3 4
A F F F F
B F F F F
and then use in Jan2 (top left F)
=SUMIFS({Peterson Family Transactions - Sum Range}, {Peterson Family Transactions - Type}, $Type@row, {Peterson Family Transactions - Date}, AND(MONTH(@cell) = Jan$1, YEAR(@cell) = $Type$1))
.
Note the use of $ to lock in column and row references. This will allow you to put the formula in the first cell and the drag-fill over and down to populate the rest of the table.
.
If you've read this far (or just skipped to the bottom haha) here's the summary...
Try to set things up so you can directly edit the formula itself as little as possible, and try to condense formulas when possible by using the AND and OR functions when the same range will be used for multiple criteria.
-
I appreciate the suggestions Paul. This is very helpful and tells me I need to simplify my formulas. I have tried the AND function like you mentioned and am still having no luck...I can't figure it out. I have similar formulas just like this one that work just fine:(. I have also verified that all my references are correct and they are.
-
Are you able to provide some screenshots?
The reason I mentioned the references is because you mentioned you are looking at the same range for both the month and year, but your ranges are labeled as 3 and 4. Unless you specifically changed the range names, they should both be the same. Either both 3 OR both 4.
I have noticed that sometimes when I am making cross sheet references, if I am moving too fast and the computer or SS a little slow, I will select a row or column, and then the range jumps back to the home cell [Column1]1. I then have to make sure that I re-select the correct range or else the formula will not work properly.
-
Hi Paul,
So here is the current formula i tried using your suggestions with still no luck. I also thought it was odd that the same date column i was referencing was coming up with a different range number so I thought I would eliminate that as possibility. I did check however before making this change and for some reason still wanted to designate it a different number:/
=SUMIFS({Peterson Family Transactions Range 2}, {Peterson Family Transactions Range 3}, "AUTO", {Peterson Family Transactions Range 1}, AND(MONTH(@CELL) = 1, YEAR(@CELL) = 2019))
Below will be a screen shot of each range I am referencing in the order it is listed in the formula. Hopefully this will help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!