Formula Help
Hello,
I am trying to figure out a formula to identify how many time a certain report was received after the last day of the month. I have included a screenshot of the data source. I would like to be able to count across the amount of times it was on time, late or not received. On time would be on or before the last day of the month, late after the last day of the month.
Thank you,
Cara
Answers
-
Hi @CaraBart28
Looking for "Not Received" will be easy as it's the same text value throughout the row. In this instance you'll want to use a COUNTIF Function to look in the range of your row:
[Jan '22 Medicaid"]@row:[Dec '22 Medicaid"]@row
^ This is assuming your last column is [Dec '22 Medicaid"]. See: Create a Cell or Column Reference in a Formula
Then see how many of those cells say "Not Recieved". This is how you would write it in a column in data source sheet:
=COUNTIF([Jan '22 Medicaid"]@row:[Dec '22 Medicaid"]@row, "Not Received")
Then the other two counts need a bit more of a customized formula. Are all of the columns Date type of columns? If so, we can use the MONTH Function to check the date in each cell against the Month of that column.
We'll need to write an individual formula per-column and add them together with the + sign, like so:
=IF(MONTH([Jan '22 Medicaid"]@row) = 1, 1) + IF(MONTH([Feb '22 Medicaid"]@row) = 2, 1) + IF(MONTH([Mar '22 Medicaid"]@row) = 3, 1)
Then once you have that count, for your instance of being received late, what I would do is count how many cells have a Date in them in this row, then subtract the previous calculation (how many dates were in the correct month) from it:
=[On Time Formula]@row - COUNTIF([Jan '22 Medicaid"]@row:[Dec '22 Medicaid"]@row, ISDATE(@cell))
Let me know if these three formulas make sense and work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!