Ignoring blanks in Countifs

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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…