CountIfs resulting in "0"

rsmith

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")

 

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!