COUNTIFS and SUMIFS formulas with multiple criteria AND a dynamic date range in a sheet summary

SteCoxy

Hi everyone, I've had a look in the forum threads to try and find an answer already, but not sure what I'm trying to do is like-for-like with some of the suggestions.

BACKGROUND: I'm helping colleagues with aggregating some of the data in their deployments sheet, which they use for managing and tracking their events, the teams who are travelling to the events, the number of days they are abroad etc.

REQUIREMENTS: They would like to know things like the number of times a staff member has been deployed overall, how many times they've been to certain countries, how many times using a dynamic date range, plus knowing how many days with these parameters applied they've been abroad.

ACTIONS: As there's over 200 staff, I felt a metrics sheet that cross references the data in the main sheet would work best due to sheet summaries being cumbersome and time consuming configuring. In a metrics sheet you can relatively quickly apply formulas to entire rows using @row. I've added in 2 sheet summaries as below, with the intention of using them in my formulas:

In the metrics sheet, my main column is Staff Member, which lists all of the department staff's names. The following columns are then intended to be used to provide the metrics:

ISSUES: When trying to cross reference the column that lists the staff deployed in the main sheet where I'm wanting the formulas to COUNT or SUM (called Team Deployed), which is currently a multiple select dropdown that isn't restricted, there's some discrepancies such as accommodation information or varying travel dates that differ from their colleagues who may be travelling at a later date. I've already explained to the team that this makes it far harder for formulas to provide metrics on what they want. I'm proposing we remove all discrepancies and standardise the values by making the column a multi-select contact list column, so that in the metrics sheet we can easily cross reference the staff member in the main sheet. I believe this would be done through using CONTAINS OR HAS?

Does the following formula appear to be right if I am looking to COUNT the overall number deployments a staff member has been booked for i.e. the overall number of times a person appears in the Team Deployed column:

=COUNTIF({Team Deployed Range}, CONTAINS([Staff Member]@row, @cell))

I also want to use a dynamic date range to COUNT how many times a staff member has been booked for that cross references 2 date columns in the main sheet Travel Out and Travel Return using a dynamic date range that would be set in the sheet summary. Is this possible? I've applied the following column:

=COUNTIFS({Team Deployed Range}, CONTAINS([Staff Member]@row, @cell), {Travel Out Range}, AND(@cell >= [Date Range (START)]#, @cell <= [Date Range (END)]#))

It's providing me with metrics that change when I amend the sheet summary date ranges, but I can't be 100% this is correct?

Finally, using that same principle, I need to calculate the total number of days the staff member has been abroad - again using a dynamic date range, so I've used the following formula:

=SUMIFS({Days in Country Range}, {Team Deployed Range}, CONTAINS([Staff Member]@row, @cell), {Travel Out Range}, AND(@cell >= [Date Range (START)]#, @cell <= [Date Range (END)]#))

Looking at the metrics sheet and checking the main sheet, I think it's doing what I need, but just wanted to check I've applied the right formula syntax/elements.

Paul Newcome

The formulas look like they should be working as expected. If needed, make a copy of the source sheet and then delete a bunch of data to the point where you only have a handful of rows that you can manipulate to ensure everythign is working as intended.

SteCoxy

Cheers Paul - that's good to know the formulas look like I've been able to crack it on first attempt. I used the ideas suggested on here to use for my sheet and I'm sure you were one of the contributors, so as ever thank you for being such a great contributor on here :-)