Help with formula: COUNTIFS

Ginny A.

I am trying to count the tasks not started yet so the column [% complete] is either blank or 0. I used this formula- COUNTIFS([% Complete]:[% Complete], "", [% Complete]:[% Complete], 0) but getting 0 as result whereas more than half of my tasks have not started yet. Any feedback highly appreciated. Thank you. 

Ginny A.

Also, related to this, when I use this formula- "=COUNT([% Complete]:[% Complete])",  it gives me 55 whereas there are 62 rows. Does it not count the parent rows? 

J. Craig Williams

Ginny,

 

COUNTIFS must match all criteria, not just one of them, so you are looking for both blank and 0, not either.

 

COUNT is not counting the number of rows for a Text/Number column, but non-blanks.

It will count the rows for a Checkbox type column (both checked and unchecked), but be careful, it will usually count the extra 9 empty rows at the bottom of the sheet too.

Verify that you have 7 blank entries in the [% Complete] column to ensure I am correct.

 

This formula gets us close - but still counts those extra blank rows at the bottom of my sheet:

 

=COUNTIF([% Complete]:[% Complete], OR(ISBLANK(@cell), @cell = 0))

 

To resolve this, I assumed that only rows with a defined task would be counted and changed the formula back to the COUNTIFS

 

=COUNTIFS([% Complete]:[% Complete], OR(ISBLANK(@cell), @cell = 0), Task:Task, NOT(ISBLANK(@cell)))

 

Hope that helps.

 

Craig

 

 

 

 

Ginny A.

Hi Craig,

 

This is very helpful! So, I tried the formula below to count the %complete "in progress" defining it as the ones that is not complete (not 100%) or not started (not 0 or blank). It seems to have worked but was wondering if there is a better way to construct the formula. I would love to hear your thoughts on it. Thank you.

 

=COUNTIFS([% Complete]:[% Complete], AND(NOT(ISBLANK(@cell)), @cell <> 1, @cell <> 0), [Task Name]:[Task Name], NOT(ISBLANK(@cell)))

J. Craig Williams

Ginny,

 

That looks OK to me.

You might be able to change <> to < and > , but that is likely cosmetic.

<> for both will result in hits on negative values or greater than 100% values.

 

Because you are using COUNTIFS, you could also get rid of the AND(), but your version is more compact.

 

You MIGHT have a misplaced end paranthesis

 

=COUNTIF(  complete%range,  AND(criterion for complete%), task-namerange, criteria for task-name)

 

Craig

 

Ginny A.

 I tried using <and> and kept getting #UNPARSEABLE. 

 

I changed it to the formula below seems to be giving the right result. I took out validating Task name as I am not counting blank cells at all. Let me know if I can improvise it further. Thanks Craig!

 

=COUNTIF([% Complete]:[% Complete], AND(NOT(ISBLANK(@cell)), @cell <> 1, @cell <> 0))

 

J. Craig Williams

Look great!

 

Craig

Ginny A.

Just curious, can you give me an example how to use < and >? I could not find any. Thanks Craig! 

J. Craig Williams

Ginny,

 

I'm not sure what the question is.

 

=COUNTIF([% Complete]:[% Complete], > 0.5)

or

=COUNTIF([% Complete]:[% Complete], @cell&gt; 0.5)

 

will count percentages greater than 50%

 

Craig

 

Chris H.

Hi All & Hopefully Craig - I had a formula work perfectly well last week, but this morning I was getting a #NO MATCH.  Data has not changed.  I do have this looking up data which is pulling from an INDEX, MATCH formula, but when I changed a piece of that source data to a value it still did not work. 

Any thoughts on this?

Here is the formula - Counting dates in the column for the month of August:

=COUNTIFS([File Upload Date]:[File Upload Date], @cell >= DATE(2018, 8, 1), [File Upload Date]:[File Upload Date], @cell <= DATE(2018, 8, 31))