Formula Help

CaraBart28

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

Formula for Report.jpg

Genevieve P.

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