CountIf formula using multiple sheet

lesmickin

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.

 

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