COUNTIF matching instances, or alternative IF(ISBLANK workaround?

Jen Castillo

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*.

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!

Jen

Paul Newcome

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

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

Jen Castillo

@Paul Newcome Thank you! This worked!

What would the formula be if I were to add in my rolling date criteria? [date], >=TODAY(-7)