Using COUNTIF in Sheet Summary

M. David · 06/15/20

I am trying to use a COUNTIF formula in the Sheet Summary. I have a column called, "Current Project Status." I want to count the total number of times the word "Operational" appears in that column. I have entered this into the field with no luck; "=COUNTIF(Current Project Status:Current Project Status,Operational)" I have tried quotation marks around the word Operational. I've tried spaces on either side of the colon. I don't know what it is, but I have terrible luck getting formulas to work on the Sheet Summary. In case this is an issue, some of the data in this column is from cell links.

Any help is appreciated.

Thanks.

Paul Newcome · 06/15/20

You are going to want to remove the space from after the = and the column names should be wrapped in square brackets.

=COUNTIF([Current Project Status]:[Current Project Status], "Operational")

sahilhq · 06/15/20

@M. David Hey! Try using the formula below - it should work for you.

= COUNTIF(Current Project Status:Current Project Status, "Operational")

Just ensure that the range of cells (the group of cells to count) you have selected is correct.

Feel free to reach me at sahilhq18@gmail.com in case you are still having any issues.

Paul Newcome · 06/15/20

You are going to want to remove the space from after the = and the column names should be wrapped in square brackets.

=COUNTIF([Current Project Status]:[Current Project Status], "Operational")

M. David · 06/15/20

Thanks for the help. The brackets [] were the issue. It's frustrating that you can't just click a column heading while writing a formula and that column be reflected within the formula. If there is a trick to doing this, please share. Thanks again for the help.

Paul Newcome · 06/15/20

@M. David The closest thing to a "trick" for this would be to click and drag to select more than one cell within the column, then manually delete the row numbers.

M. David · 06/15/20

@Paul Newcome That makes sense. I will do that from now on. Thanks.

Paul Newcome · 06/15/20

@M. David Happy to help! 👍️ I use this when selecting any range whether it be a group of cells, entire column, a row across multiple columns, etc whenever my column names are a little weird or long. Even regular cell references where I replace the row number with "@row". I just find it is a little safer and helps prevent typos.

Ross Novotny · 12/08/20

I am working on a similar formula, but I am trying to count the number of leads in the current year. I am getting an Invalid Data error when I use this formula as a sheet summary:

=COUNTIF(YEAR([Lead Creation Date]:[Lead Creation Date]), YEAR(TODAY()))

Any idea how to get this corrected?

Andrée Starå · 12/08/20

Hi @Ross Novotny

Try something like this.

=COUNTIF([Lead Creation Date]:[Lead Creation Date],IFERROR(YEAR@cell),0)=YEAR(TODAY)

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!