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"?
