COUNTIF matching instances, or alternative IF(ISBLANK workaround?
Hello!
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*.
 =COUNTIFS([inspect]:[inspect], >0, [total]:[total], [inspect]@row = [total]@row)
 =COUNTIFS([inspect]:[inspect], ="", [total]:[total], [inspect]@row = [total]@row) (using ="" instead of >0?)
 =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 workaround 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 workaround is also not working!
Thank you for your help!
Jen
Best Answer

You won't be able to compare the two columns directly in the COUNTIFS. you will need to use the Match column. Try this formula there...
=IF(Total@row <> "", IF(Total@row = Inspect@row, "Yes", "No"))
Answers

You won't be able to compare the two columns directly in the COUNTIFS. you will need to use the Match column. Try this formula there...
=IF(Total@row <> "", IF(Total@row = Inspect@row, "Yes", "No"))

@Paul Newcome Thank you! This worked!
What would the formula be if I were to add in my rolling date criteria? [date], >=TODAY(7)
Help Article Resources
Categories
Check out the Formula Handbook template!