collecting data between dates

Ashley1987

Hello,

I am trying to cross-reference a sheet and collect data based on multiple criteria, including whether a date falls between a date range.

Ex: I have team members allocated to multiple project at different percentages for different dates. I am trying to pull the % allocated based on the team members name, the client and whether or not the period falls between a date range.

I want to pull % Allocation into a new sheet if the Assigned to and Client columns match my criteria and if the the week of Dec 11, 2023 to Dec 15th, 2023 fall between the Start Date and End Date.

Lucas Rayala

Hi @Ashley1987 you can do that with an INDEX COLLECT function combination:

=INDEX(COLLECT({% Allocation}, {CLIENT}, Client@row, {Assigned to}, [Assigned to]@row, {Start date}, >=DATE(2023,12,11), {End date}, <=DATE(2023,12,15)),1)

Ashley1987

Hi Lucas,

I tried your formula but got an error message #invalid value.

I need to be more specific. I am actually looking to pull the average allocation % for a date range per resource since some people work on multiple clients at the same time. This information will be pulled from the data sheet and put into the reporting sheet.

Ex: What % allocated is John Doe from Dec 11 to Dec 15?

Answer: 75% - this is the information i need to show up in the reporting sheet

Data sheet:

Reporting sheet:

Ashley1987

Was anyone able to help me with this?

Lucas Rayala

Hi @Ashley1987 we can’t see that you reply if you don’t @ mention us. If you need to get the average of something based on specific criteria, you’ll want to use the function combination AVG/COLLECT

Here’s a link to all the functions with links to description articles:

https://help.smartsheet.com/functions