Forum Discussion
Help with Excel formula
As variant for such layout
add first list of months as
=UNIQUE(EOMONTH(+Source[Date], 0))and against each of them
=SUM(
(EOMONTH(+Source[Date], 0)=$F3)*
( Source[Shifts recruited]>=0.8*Source[2024 Target] )
)
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"?