Forum Discussion
Help with Excel formula
Thank you SergeiBaklan and HansVogelaar
In other COUNTIFS and SUMIFS formula's I've been using: Events!$B:$B,">="&DATE(2024,4,1),Events!$B:$B,"<="&DATE(2024,4,31) as a condition/criteria covering the required date range.
Can this same condition be used in the relevant formula needed to compare the 'target' and 'recruited' columns?
The stats sit in another sheet in a table that provides a breakdown of info per month. E.g.
| Jan | Feb | Mar | Apr | May | Jun | Jul | |
| No. Events | 3 | 2 | 5 | 11 | 11 | 8 | 5 |
| No. Days | 4 | 3 | 6 | 14 | 17 | ||
| New Events | 3 | 2 | 4 | 3 | 4 | ||
| Recruitment Target | 16 | 42 | 54 | 219 | 260 | ||
| Shifts filled against target | 16 | 35 | 28 | 111 | 86 | ||
| % of Target reached | 100% | 83% | 52% | 51% | 33% | ||
| Cumulative % | 100% | 88% | 71% | 57% | 47% | ||
| % Events over 80% full | |||||||
| % Events over 90% full | |||||||
| % Events over 100% full |
The formula that pulls through the no. of new events is =COUNTIFS(Events!B:B,">="&DATE(2024,5,1),Events!B:B,"<="&DATE(2024,5,31),Events!G:G,"NEW")
For what I need, all the data sits in an Events tab and the date is in column B, the target in column H and the count of recruitment so far is column J.
Criteria could be used even if that's not optimal to reference entire column.
As for the stats do you use month names as texts or as dates formatted as "mmm"?