COUNTIF matching instances, or alternative IF(ISBLANK workaround?



I'm struggling with syntax and can't figure out why this isn't working.

I have two columns [Total] and [Inspected]. I'm using countif in the first row of [Column4] to count the number of instances both columns have matching values, but it isn't giving me the correct count. Eventually, I'd like to build upon it to only count things with a delivery date/[Date] in the last [x] days*.

  1. =COUNTIFS([inspect]:[inspect], >0, [total]:[total], [inspect]@row = [total]@row)
  2. =COUNTIFS([inspect]:[inspect], ="", [total]:[total], [inspect]@row = [total]@row) (using ="" instead of >0?)
  3. =COUNTIFS([inspect]:[inspect], ="", [total]:[total], [inspect]@row = [total]@row, [date], >=TODAY(-7))

(**this will be weekly, monthly, quarterly, and fiscal year. I know how to write the formula for rolling -7 days/-30 days, but the quarterly/fiscal year... I'll cross that bridge when I get to it, ha!)

Additionally, the work-around I've tried to implement is stumping me. I created a column titled [Match] to generate Yes/No if it was true or false.

=IFERROR(IF(total@row = inspect@row, "Yes"), "No") worked, but it counts all my as well and counts them as "true". When I tried to add an ISBLANK.... everything (I) fell apart. :(

=IF((ISBLANK([MATCH]@row ""), "", total@row = inspect@row, "Yes"), "No" )

I'd prefer to just =countif, but for learning purposes, I'd like to know why my failed work-around is also not working!

Thank you for your help!



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!