Count specific Files due to complete each month.
I have a Target Complete Date for each type of file (over 1600)
I need to capture the number of files Due to complete in Jan, Feb, etc
Each month.
Below I am trying to count IF any Applied Bank Files are due to complete in January. The first off sheet reference is the file name column, the second off sheet reference is the Due to Complete date column.
=COUNTIFS({Remitco FIle Range 3},"Applied Bank',{Remitco FIle Range 4},>=DATE(2020,01,01),AND<=DATE(2020,01,31))
Answers
-
I have to report on this for EACH month separately.
Next they want me to report the ACTUAL completed within each month.
-
I tried to add additional information and it changed it to answered, then when I said "no it was not answered" - it changed it to rejected. (new to this)
-
Hi Tammy,
You've almost got it... counting between a date range can be difficult. Try this:
=COUNTIFS({Remitco FIle Range 3}, "Applied Bank", {Remitco FIle Range 4}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 01, 31)))
COUNTIFS work by stating the {range} then "Criteria", then {range}, then "Criteria". In this instance, you have two criteria for your second range (between this and this) so I've added an AND afterwards. But then, within an AND we still need to have a reference... which is where the @cell comes in. This says to look within each cell of the previously stated range and search for these dates.
For February all you would need to do is change the 01 to be 02 in each DATE function to look for the next month. Then if you're looking for a different range & criteria, you can just add in another statement at the very beginning (such as a certain column saying "Complete" or a checkbox being checked if it's done, etc).
Here are some Help Articles I used:
Let me know if you have any questions about this!
Cheers,
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!