Counting blank cells when the column is being used for roll up metrics in another sheet

Laurie A

Hi All,

I'm working on creating dynamic metrics using a roll up metrics sheet. I'd like show the total number of tasks for each status and the total that has no status assigned. Easy, right? =COUNTIF({Column Name},"Status Type") or =COUNTIF({Column Name},"") the problem is the total number of tasks by status type exceeds the total number of tasks.

The overage is 10 and I believe it is because the Status Type column is being referenced as part of a formula on another sheet, so the last 10 rows in the sheet have the grey triangle on the right hand side of the cell (popup says "Used in formula on:..." when you hover over it). So my guess is that because of this, they are considered 'active' cells and get counted in my count blank formula.

So my question is how can I get an accurate count of the truly blank cells. I have a Row ID column that is set up as an auto-number system column that I think I can use. Essentially I need a COUNTIFS formula that counts only the rows where the 'Status Type' column is blank AND the 'Row ID' column is NOT blank.

Can anyone help me out with this? I think it needs to be =COUNTIFS({Status Type},"",{Row ID}, NOT(ISBLANK())) but that doesn't work.

Thanks in advance.

David Joyeuse

Hi @Laurie A

Try this:

=COUNTIFS({Status Type},"",{Row ID}, NOT(ISBLANK(@cell)))

Laurie A

Hi David,

That didn't work, it gives me an 'Incorrect Argument' error. Any other thoughts?

Laurie

Laurie A

Any other thoughts on this?

@Paul Newcome, you are a formula guru who has helped me before. Any thoughts?

Surely there is a way to get an accurate count from a column that is being used in a formula on another sheet.

Genevieve P.

Hi @Laurie A

Can you copy/paste the exact formula you're using? David's response above should have worked. "Incorrect argument" could mean that something is in the wrong order, or that something is missing (like a criteria).

It would be helpful to see a screen capture of your formula in use on the sheet, and of each range in the pop-up range window when you click on "edit reference"... but please block out any sensitive data from the images.

Thanks!

Genevieve

Paul Newcome

It is because every sheet automatically has 10 rows added to the bottom of it (until you reach the row limit). Counting blank cells will include these 10 blank rows, so you should be able to just subtract 10 from your count of blanks and be good to go.

=COUNTIF({Column Name},"") - 10

If you wanted to be able to specify that the Row ID is not blank, I would suggest this...

=COUNTIFS({Status Type}, "", {Row ID}, @cell <> "")

If you are still receiving the incorrect argument error. Double check that your ranges are both covering the entire column by clicking on the column headers when creating your cross sheet references.

Sometimes it takes an extra second or two for the sheet to load in the window when selecting your range. If you select your range before it is completely loaded, when it does finally load the selection will revert back to the home cell (top left cell in the sheet) and you have to reselect your column.