Welcome!

CountIf formula using multiple sheet

I am trying to do a formula to capture all empty fields in a particular column on a separate sheet. but I keep getting the unparseable error.

If i select the entire column I get a count including empty rows. Currently my formula is:

=COUNTIF({Compliance Management Range 1}, "")

This is allowing for selected criteria only and not the entire column. I need this count to change each time I add a new row, so I need to use the entire column but to not include rows that are currently blank.

Find more posts tagged with

Comments

Genevieve P.

One way you could do this would be to create a helper column in the source sheet. This column would only be there to reference the cell @row in your [Compliance Management Range] column and use an ISBLANK formula to see if the cell is blank or not. You could have the same IF(ISBLANK sample formula structure that's in the Help Center article (here.)

Drag-fill the formula down to your lowest row with content in it, but no further. As new rows populate, this formula will be carried down (as long as it's in 2 cells above the new row).

Then, in the destination sheet, you could alter the COUNTIF formula to be looking at that helper column to count how many rows say "Cell is blank".

Let me know if you would like any further explanations or screen captures!

Cheers,

Genevieve

Khanambano

HI @Genevieve P. i know this is an old thread but i was researching to see if someone had this problem already.

Im trying to use Countif formula to count all "Completed" Projects from several sheets:

All the sheets are identical and the column i am trying to pull from is the same "Delay Status" but i keep getting INCORRECT ARGUMENT SET - any help would be appreciated, not sure what i am missing here.

Genevieve P.

Hi @Khanambano

You'll want to add together multiple, individual COUNTIF statements, like so:

=COUNTIF({Sheet 1 Range 1}, [Column1]@row) + COUNTIF({Sheet 2 Range 1}, [Column1]@row) + COUNTIF({Sheet 3 Range 1}, [Column1]@row)

And so on. Does that make sense?

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…