How Many Times an Item was Sent by a Specific Date
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!
Best Answer
-
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.
Answers
-
Are you able to provide a screenshot of the source data for reference?
-
Hi Paul, here is the screenshot of the data source sheet.
-
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?
-
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.
-
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.
-
Thank you Paul. Could I do these as a sheet summary and then use the sheet summary for the widgets?
-
@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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!