CountIfs resulting in "0"

Similar to my previous post, I'm now running into issues with CountIfs returning zeros when I am referencing multiple columns from a separate sheet. I am trying to figure out the number of "Risks" and "Issues" that are marked as state "Complete" each week. I was able to count how many risks and issues have occurred each week, but when I added the "Complete" criteria, smartsheet returns zeros even though I know there are values there. Here is the formula I've been using:

=COUNTIFS({Date Assigned}, ">=DATE(2019, 03, 25)", {Date Assigned}, "<=DATE(2019,03,31)", {Type}, "Issue", {State}, "Complete")

Find more posts tagged with

Formulas

Comments

sean59916

Hi -

You need to remove the quotes from around the date statements - to be this:

=COUNTIFS({Date Assigned}, >=DATE(2019, 03, 25), {Date Assigned}, <=DATE(2019,03,31), {Type}, "Issue", {State}, "Complete")

I hope this helps?

Regards,

Sean

Paul Newcome

Sean is correct. To expand on that...

When you wrap something in quotes, it is looking for that EXACT text string. If whatever is inside of those quotes is not actually displayed in a cell, it will return a zero for your count.

rsmith

Perfect, that fixed the issue! Thank you

rsmith

That makes perfect sense, changing the quotes made the correct data appear. Thank you again!

Paul Newcome

Happy to explain!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…