How to have a formula reference a blank report?

lauren.waters111676

(FYI I do not have summary sheet access)

I have a sheet that has a list of open Job opportunities. Of course sometimes that sheet is blank because there are no openings. I created a second sheet for metrics that I could use to put a counter-widget in my dashboard. That is, in my dashboard there would be a metric widget showing the number of open opportunities. However, in my "metrics" sheet, where i put in the formula "Countif" and was simply going to select a column in my opporutnity sheet to count if anything was in those rows, I get the error that says "this sheet is blank, you can't use it"... Yeah I know it's blank, but 0 is valid data.....

Paul Newcome

Enter a "dummy" row that never gets deleted.

lauren.waters111676

:/ not ideal, then people see the dummy row...and have to work the formula so it still reports 0 not 1....but good thought i'll work with that

Paul Newcome

What formula are you currently using?

lauren.waters111676

So I entered a "." in the title column and turned the font white so you can't see it. Regarding the formula, i then selected a dropdown list column with only a few choices and did the following (the point is, it's blank... so it will return 0)

=COUNTIFS({SE-RAP Open Opportunities Occupational Category}, "managerial", {SE-RAP Open Opportunities Occupational Category}, "analytical", {SE-RAP Open Opportunities Occupational Category}, "technical", {SE-RAP Open Opportunities Occupational Category}, "administrative", {SE-RAP Open Opportunities Occupational Category}, "scientific")

Paul Newcome

Is that working for you? Depending on the size of your sheet and the complexity of your formulas, there are ways to make the formula a little more efficient, but... If you are not noticing a slow down in performance and it is working the way you need it to, then there is no real need to change things up. Just checking to make sure you're good to go.

lauren.waters111676

Thanks so much for checking, it is working :) but if you have a more efficient formula that's great. Really, if there is ANYTHING in that column I want it tallied, but i didn't know how to insert count anything/all etc.

Paul Newcome

Ok. So the first thing would be that if you are referencing the same range for multiple sets of criteria, you can actually use an AND or an OR statement depending on your needs so that the cross sheet reference is only used once in the formula instead of multiple times. This also helps keep things organized in more complex formulas with multiple different cross sheet references. It also can dramatically cut down on keystrokes as you will see below.

Original Formula:

=COUNTIFS({SE-RAP Open Opportunities Occupational Category}, "managerial", {SE-RAP Open Opportunities Occupational Category}, "analytical", {SE-RAP Open Opportunities Occupational Category}, "technical", {SE-RAP Open Opportunities Occupational Category}, "administrative", {SE-RAP Open Opportunities Occupational Category}, "scientific")

Using an OR Function:

=COUNTIFS({SE-RAP Open Opportunities Occupational Category}, OR(@cell = "managerial", @cell = "analytical", @cell = "technical", @cell = "administrative", @cell = "scientific"))

But if you want to count everything that is not blank, then we can actually specify that instead of counting all of the other possibilities which shortens things even more. This also allows you to select any column regardless of what selections can possibly be made and what data type it is.

Counting "non-blanks":

=COUNTIFS({SE-RAP Open Opportunities Occupational Category}, <> "")

lauren.waters111676

OH THANK YOU!

Paul Newcome

Happy to help! 👍️