Sheet Summary COUNTIF

I'm having problems using the COUNTIF function in the new-ish sheet summary. Everytime I sort rows it changes the range. For example, in a summary text field I have

=COUNTIF([Column A]1:[Column A]79, =0)

After sorting by a column it becomes

=COUNTIF([Column A]29:[Column A]43, =0). Or something similar.

The sheet summary is supposed to be used for gathering metrics. But I can't use it for this intended purpose if it means no longer sorting the sheet. Anyone have advice? This can't be the way its meant to work.

Thanks!

Find more posts tagged with

Sheets

Formulas

Comments

Ella

Try this =COUNTIF([Column A]:[Column A], =0)

lmarchisio

Thanks Ella, that solved part of it. I'm not getting random ranges anymore. But the formulas are counting the blank rows in addition to those that meet my =0 condition. Smartsheet customer support is looking for answers

Ella

I will appreciate it if you post after they looked into it and how it was resolved.

Paul Newcome

Try switching to a COUNTIFS and include additional criteria to specify not blank.

If you are counting how many are actually numbers equaling zero:

=COUNTIFS([Column A]:[Column A], AND(NOT(ISBLANK(@cell)), @cell = 0))

If you are counting unchecked boxes (or unflagged flags or anything else that uses a 1/0 for true/false)

=COUNTIFS([Column A]:[Column A], = 0, [Different Column]:[Different Column], NOT(ISBLANK(@cell)))

Just replace the [Other Column] with some column that will always have a value in the cell.

This will allow you to reference the entire column and exclude the blanks.

lmarchisio

Paul has it 99%. COUNTIFS is throwing an incorrect argument error when using NOT(ISBLANK()) as a criteria. The documentation for the function says operators are limited to =, <, >, <>, <= and =>. Which i think may have something to do with it. The final function ended up being:

=COUNTIFS([Column A]:[Column A], 0, [Column B]:[Column B], <>"")

Where, as Paul says, Column B needs to be something that will always be non-empty.

One other note, the online documentation for COUNTIFS distinctly mentions that blank cells should not be counted when using this function. Smartsheet indicated that counting blank checkboxes was "an expected behavior". I've asked them to indicate this in the write-up of the function.

Paul Newcome

The incorrect argument error may have been thrown because you did not use an @cell reference.

I use NOT(ISBLANK(@cell)) within COUNTIFS functions on almost every single sheet I create as it is part of how I replicate the row number.

=COUNTIFS([Column Name]:[Column Name], OR(NOT(ISBLANK(@cell)), ISBLANK(@cell)))

Works like a charm every time as long as you use the @cell.

Ella

Hey Paul, thank you for sharing your solution! Is there a difference between @cell or @row?

Paul Newcome

Very much so.

@row tells a formula to look at whatever row the formula is on. It will replace the row number in a cell reference.

@cell basically tells a formula to look at each individual cell within a range as opposed to looking at the range as a whole.

HERE is some documentation on both.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…