Ignoring blanks in Countifs

bpeters

I am trying to create a formula to look at multiple columns and count if they meet a specific criteria.  I have a formula that works when there is data in the sheet but it causes an error whenever data is missing.  I need to create a template that can be used to create several trackers so I need the formula to work when there is data missing from some of the rows.  

The formula is: 

=COUNTIFS([Graduation Date]303:[Graduation Date]308, YEAR(@cell) = 2018, Complete303:Complete308, 1, [EVS/DS]303:[EVS/DS]308, "EVS", State303:State308,"MD")

The moment I extended any range to look from row 303 to row 1600, I get an invalid data type error.  I need the formula to work even when there are blank rows because they will have data in them at some point.  

Not sure if I am articulating this properly but if anyone can help I would greatly appreciate it. 

 

Mike Wilday

Could you share a screenshot of your sheet so we can see how the sheet is set up? 

Paul Newcome

Try wrapping it in an IFERROR function like this:

 

=IFERROR(COUNTIFS([Graduation Date]303:[Graduation Date]308, YEAR(@cell) = 2018, Complete303:Complete308, 1, [EVS/DS]303:[EVS/DS]308, "EVS", State303:State308,"MD"), "")

 

What this does is leave the cell blank if there is an error.

Mike Wilday

I don't know if that will work though. Because he is doing a COUNTIFS statement and wants to return the result. If the result is an error they won't get any count, just a blank cell. I think he wants his COUNTIFS to ignore errors where there is no date... you may want to wrap your Year formula in an IFERROR statement instead. You are probably getting an error in cells where there is no date. 

Try this: 

=COUNTIFS([Graduation Date]:[Graduation Date], IFERROR(YEAR(@cell), "-") = 2018, Complete:Complete, 1, [EVS/DS]:[EVS/DS], "EVS", State:State, "MD")

This is returning a dash in the year column where it can't find a date. And therefore not counting it. Attached is screenshot showing 1 row that matches the criteria and counting everything in the entire column. If you want to limit the results to specific rows, you can add the row numbers back in. But if this document is a living record you may want to count infinitely. Let me know if you have any questions. 

 

Paul Newcome

Thanks for straightening out my mess (again). I swear I just need to take a break from Smartsheet for a while. My brain is totally fried.

bpeters

Hello All, 

Thank you for the responses but I believe I figured it out.  Here is my updated formula:

=COUNTIFS([Graduation Date]763:[Graduation Date]2083, <>"", [Graduation Date]763:[Graduation Date]2083, YEAR(@cell) = 2018, [EVS/DS]763:[EVS/DS]2083, "EVS", [EVS/DS]763:[EVS/DS]2083, <>"", State763:State2083, "MD", State763:State2083, <>"")

 

So far it seems to be working but I am going to test it out a little more to make sure I do not have any issues. 

Mike Wilday

SmartSheet does have that effect on the brain. 

Paul Newcome

bpeters: Fair warning...

Mike Wilday

@Bpeters: your formula might work, but i would manually check your numbers. Adding NOT BLANK and a specific state might cause you issues. I found that adding the ISERROR worked in my instance. The error is caused by some rows not having the date.