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 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
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!