Forum Discussion
Help with Excel formula
Hello
I'm trying to insert a formula into a spreadsheet to show what % of events are 80% or higher of our recruitment target. We need it to:
- Search within a date range,
- Compare the value in the recruitment column to the value in the target column, and
- Return a result of how many rows are =>80% of the target, per month.
| Date | 2024 Target | Shifts recruited |
| 13/01/2024 | 4 | 4 |
| 20/01/2024 | 2 | 2 |
| 27/01/2024 | 5 | 5 |
| 28/01/2024 | 5 | 5 |
| 03/02/2024 | 12 | 9 |
| 25/02/2024 | 20 | 18 |
| 02/03/2024 | 10 | 8 |
| 03/03/2024 | 10 | 7 |
| 03/03/2024 | 10 | 8 |
| 17/03/2024 | 10 | 5 |
| 23/03/2024 | 6 | 0 |
| 29/03/2024 | 8 | 0 |
| 07/04/2024 | 10 | 6 |
| 07/04/2024 | 30 | 20 |
| 07/04/2024 | 20 | 4 |
| 14/04/2024 | 10 | 10 |
| 17/04/2024 | 2 | 2 |
| 18/04/2024 | 2 | 1 |
| 19/04/2024 | 2 | 0 |
| 20/04/2024 | 2 | 0 |
| 20/04/2024 | 6 | 2 |
| 21/04/2024 | 100 | 40 |
| 27/04/2024 | 16 | 7 |
| 27/04/2024 | 6 | 6 |
| 27/04/2024 | 8 | 8 |
| 28/04/2024 | 5 | 5 |
Any advice or tips on how to do this and what function I should be using?
Thank you!
12 Replies
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"?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HansVogelaar I've added an example table into the original post.
Thanks for your help.With your sample data starting in A1:
=SUM((A2:A27>=F1)*(A2:A27<=F2)*(C2:C27>=80%*B2:B27))/SUM((A2:A27>=F1)*(A2:A27<=F2))
Format the cell with this formula as a percentage.
