Can I use @row in this formula?
=IFERROR(COUNTIFS([Expected Close Date]:[Expected Close Date], YEAR(@cell) = 2020, [Sales Stage]:[Sales Stage], "A"), "Check for 'Blanks' in Forecast, Probability or Close Date")
=IFERROR(COUNTIFS([Expected Close Date]@row, YEAR(@cell) = 2020, [Sales Stage]:[Sales Stage], "A"), "Check for 'Blanks' in Forecast, Probability or Close Date"). I get an "unparseable" error.
One reason for this is I have formulas that use an entire column so that when additional rows are added, they pick up the formula. But, those formulas return errors WHEN BLANK ROWS ARE PRESENT. Smartsheet automatically adds 10 rows to a sheet when it's loaded, it seems. When I delete those rows, the errors go away, but upon the next loading of the sheet, the errors return. Why does Smartsheet add these rows?
Even stranger, I'm using COUNTIFS and SUMIFS on the same information, and only the COUNTIFS get this error.
Thanks for the help.
Jason Wirl
Best Answer
-
It is a default built into Smartsheet. There is no way to permanently remove the 10 blank rows at the bottom of the sheet.
Based on this formula from your original post:
=IFERROR(COUNTIFS([Expected Close Date]:[Expected Close Date], YEAR(@cell) = 2020, [Sales Stage]:[Sales Stage], "A"), "Check for 'Blanks' in Forecast, Probability or Close Date")
The only portion that will cause an error when a blank row is present is the YEAR function. That is why I wrapped that portion specifically in the IFERROR. So that the COUNTIFS will still count as it is supposed to and will ignore those blank rows that would cause an error.
=COUNTIFS([Expected Close Date]:[Expected Close Date], IFERROR(YEAR(@cell), 0) = 2020, [Sales Stage]:[Sales Stage], "A")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You are going to need to use the IFERROR on the YEAR portion. That is the part that is triggering the error.
=COUNTIFS([Expected Close Date]:[Expected Close Date], IFERROR(YEAR(@cell), 0) = 2020, [Sales Stage]:[Sales Stage], "A")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Thanks, but I'm not following you completely:
1) What if I want to check all the columns in the formula and make sure they aren't blank? Do I need an IFERROR for each of them?
2) Where does the error message go? "Check for 'Blanks' in Forecast, Probability or Close Date"
3) Why does my formula for SUMIFS work then? Along the line of my first question, I think the following isn't really an "OR." It seems that it only errors if ALL of them are blank. So, does the IFERROR need to be in the formula three times?
=IFERROR(SUMIFS([Weighted Forecast]:[Weighted Forecast], [Expected Close Date]:[Expected Close Date], YEAR(@cell) = 2020, [Sales Stage]:[Sales Stage], "W"), "Check for 'Blanks' in Forecast, Probability or Close Date")
This seems to be more complicated than I thought it would be when I started to incorporate the error checking.
Thanks for your help,
Jason
-
I don't understand what you are trying to do...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
OK...Sorry. I'm probably putting too much into one thread.
Let's start with the ERROR then: I have formulas that use an entire column so that when additional rows are added, they pick up the formula. But, those formulas return errors WHEN BLANK ROWS ARE PRESENT.
The reason is that Smartsheet seems to automatically adds 10 rows to a sheet when it's loaded. Is that by design? When I delete those rows, the errors go away, but upon the next loading of the sheet, the errors return. Why does Smartsheet add these rows? I get this in multiple browsers.
-
It is a default built into Smartsheet. There is no way to permanently remove the 10 blank rows at the bottom of the sheet.
Based on this formula from your original post:
=IFERROR(COUNTIFS([Expected Close Date]:[Expected Close Date], YEAR(@cell) = 2020, [Sales Stage]:[Sales Stage], "A"), "Check for 'Blanks' in Forecast, Probability or Close Date")
The only portion that will cause an error when a blank row is present is the YEAR function. That is why I wrapped that portion specifically in the IFERROR. So that the COUNTIFS will still count as it is supposed to and will ignore those blank rows that would cause an error.
=COUNTIFS([Expected Close Date]:[Expected Close Date], IFERROR(YEAR(@cell), 0) = 2020, [Sales Stage]:[Sales Stage], "A")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome There was a response here that I read, and when I went to flag it as insightful, it disappeared. I can't see it any longer. Trying to understand what you said better.
-
Is it the one that got flagged as "Best Answer" and moved to the top of the thread?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes. Sorry. I didn't know it did that. Thank you.
-
No worries at all. You said you were trying to understand something better. Which bit(s) need further explanation? I'd be happy to either try to explain in a different way or expand on something as needed.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome OK...I'll take you up on that offer:
I now understand the blank rows and the formula that throws the error. Thanks for clarifying that for me. Do you happen to know why Smartsheet does that with the blank rows? I'm more just curious than anything.
The goal for all of this is to have error checking and return the "Check for blanks..." if any of my reps leave Forecast Amount, Probability, or Expected Close Date blank. I don't think that they'd necessarily look at the Sheet Summary, but I'd at least have something more descriptive on the dashboard (although I haven't gotten to that part yet - if you have suggestion on how I'd maybe have the chart display something that would let a user know to go to the sheet because it can't display what's intended due to a, in this case, blank field). I'd do this for almost all of the Summary calculations because almost all of them are derived from this simple input and then the three locked columns you see below in my screenshot.
We measure more by quarter than month, so I used a variation of a "Quarter" Calculation I saw you had helped someone with since Smartsheet doesn't have one built-in (and I can understand why as the definition of quarter varies so much by business). I'm using that for a few of my calculations so I then don't have to add multiple MONTHS together in a formula.
I really appreciate the direct help, and what you've provided in these forums. One other thread I'm currently looking at and that's next on my list is to use COLLECT to return a DISTINCT count of "Opportunities." In the Type column, you can see that there's usually two components, Software and Services, but they are really just one opportunity as they happen at the same time. So, I'm going to count it once for each DISTINCT account name.
I look forward to your response. They are always helpful.
-
Those 10 extra rows are just something that Smartsheet does. The only time they aren't there is if you have a sheet that has the maximum number of allowable rows.
For indicating the blank rows on a dashboard...
I can think of a few different possibilities from a very basic count of how many rows have a blank to listing out the row number, Account Name, Account Rep, and the name(s) of the blank field(s).
The solutions involve anywhere from no "helper" columns to multiple "helper" columns.
So if you tell me what you want to see on the dashboard, then we can start working towards a solution for that as well.
I would suggest starting a new thread for that as it is a pretty different topic from this one (you can @mention me in the new thread if you would like).
Getting the distinct count based on criteria in a different column would look something along the lines of...
=COUNT(DISTINCT(COLLECT(range_to_count, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria, ..........................)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Lauren Kennedy My community "friend" @Paul Newcome has helped me (actually us since it's all Itasca-related) with several questions - some of the very trivial variety. You can ask him about further help in this thread.
Jason
-
@Jason Wirl Haha. Thanks for the vote of confidence. I do what I can.
@Lauren Kennedy Feel free to ask away, but if it is not related to the original post above then I would suggest creating a new thread. That helps keep things on topic for others searching for similar solutions. You are more than welcome to "@ mention" me either in a comment or post to get my attention, and I will do my best to help.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Curious, do you do any contract work for Smartsheet users? We've got some specific things we need done but don't have all the expertise necessary we are finding out. If you don't, do you know of a resource where we could find some folks who can help in this way?
Thanks...
-
@Jason Wirl I wish I did. It seems like I am missing out on a little bit of a paycheck by not. Haha.
I have seen a few people advertise their services here within the community, but the only person I can think of right off would be @Andrée Starå. I am fairly certain he does that kind of thing.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!