How Many Times an Item was Sent by a Specific Date

CaraBart28

Hi,

I am working on a project where we are trying to identify how many time a report was sent by the last day of the month, after the last day of the month and not sent at all. I am struggling on how to put this together because we would like to be able to display this information on the dashboard. I have included a screen shot of the report I am working on.

For Example we would like to know how many times "Member Files" was sent by the last day of each month, after the last day of each month, or not at all.

Thank you!

Paul Newcome

You would need a COUNTIFS.

This would tell you how many Jan 22 reports were sent prior to Jan 31.

=COUNTIFS({Source Sheet Jan 22 Column}, @cell<= DATE(2022, 01, 31))

You would change the DATE portion to be greater than for those that were sent late or blank (double quotes - "") for those that were never sent.

Paul Newcome

Are you able to provide a screenshot of the source data for reference?

CaraBart28

Hi Paul, here is the screenshot of the data source sheet.

2022-09-12_14-04-22.jpg

Paul Newcome

So you want to count how many times the January 2022 column has a date that is before the 31st, after the 31st and blank?

CaraBart28

That is correct, and we would also like to be able to count across to see how many times the particular report (row) was sent before the last day of the month, late, or blank.

Paul Newcome

You would need a COUNTIFS.

This would tell you how many Jan 22 reports were sent prior to Jan 31.

=COUNTIFS({Source Sheet Jan 22 Column}, @cell<= DATE(2022, 01, 31))

You would change the DATE portion to be greater than for those that were sent late or blank (double quotes - "") for those that were never sent.

CaraBart28

Thank you Paul. Could I do these as a sheet summary and then use the sheet summary for the widgets?

CaraBart28

@Paul Newcome Ok, so I was able to get the formula to work!! Is there a way to copy the formula across the cells rather than having to type it in for each cell for each month for received on time, received after the date, or not received? I included three screenshots, one without the formula and the other with the formula, as well as one of the data source. As well is there a way to count across to see how many time the particular report was sent on time, late, or not at all?

Data Source.png

ACO Metrics with Formula.jpg

ACO Metrics.png