Countifs with a range based on greater than and less than

Jay Klomp

I'm having an issue writing a formula that I thought would be pretty easy. Essentially, I have a Sheet Summary of Tasks In Progress that I want to count all rows that have "Task" checked and "Progress" is less than 100% and more than 0%. My formula for Tasks Not Started works by counting only the 0% and my formula for Tasks Completed works by the 100%. I've tried several different formulas (including AND) to try to get Tasks in Progress to count everything that has "Task" checked and "Progress" displaying anything except 0% and 100% but I always get either 0 rows or all the rows.

Screen Shot 2020-09-01 at 1.39.13 PM.png

Screen Shot 2020-09-01 at 1.39.33 PM.png

Screen Shot 2020-09-01 at 1.39.50 PM.png

Screen Shot 2020-09-01 at 1.40.26 PM.png

Andrée Starå

@Jay Klomp

Try something like this.

=COUNTIFS(Task:Task, 1, Progress:Progress, <>0, Progress:Progress, <>1)

Did it work?

Andrée Starå

Hi @Jay Klomp

Start by removing the ”” around the number for the percentage.

When you add ”” it’s read as text.

Did that work?

I hope that helps!

Be safe and have a fantastic day!

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!

Jay Klomp

@Andrée Starå

No. I've done that too. When I do that, it counts the 100%s as well. See attached.

Screen Shot 2020-09-01 at 2.31.45 PM.png

Andrée Starå

@Jay Klomp

Try something like this.

=COUNTIFS(Task:Task, 1, Progress:Progress, <>0, Progress:Progress, <>1)

Did it work?

Jay Klomp

@Andrée Starå

Thanks! That worked great.

Andrée Starå

@Jay Klomp

Excellent!

You're more than welcome!