Can I use @row in this formula?

Jason Wirl

=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

Paul Newcome

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")

Paul Newcome

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")

Jason Wirl

@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

Paul Newcome

I don't understand what you are trying to do...

Jason Wirl

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.

Paul Newcome

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")

Jason Wirl

@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.

Paul Newcome

Is it the one that got flagged as "Best Answer" and moved to the top of the thread?

Jason Wirl

Yes. Sorry. I didn't know it did that. Thank you.

Paul Newcome

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.

Jason Wirl

@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.

Paul Newcome

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, ..........................)))

Jason Wirl

@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

Paul Newcome

@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.

Jason Wirl

@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...

Paul Newcome

@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.