Sum up values if it falls within a period

HuiXian

Hi all

Im trying to have a formula to sum up the allocation % if it falls within a specific date range with the sheet summary function. However, I returned an error. Im not exactly sure where i went wrong.

Formula used:

=SUM(COLLECT([Allocation % - SS]:[Allocation % - SS], [Start Date]:[Start Date], >=(2023, 1, 1), [End Date]:[End Date], <=DATE(2023, 3, 31)))

Thank you for advising!

Cheers

HuiXian

Paul Newcome

You forgot to include the DATE function for the first date criteria.

HuiXian

Hi Paul, sharp eyes! i corrected the formula but i got an invalid value :(

is it the way the formula is constructed?

Corrected formula

SUM(COLLECT([Allocation % - SS]:[Allocation % - SS], [Start Date]:[Start Date], >=DATE(2023, 1, 1), [End Date]:[End Date], <=DATE(2023, 3, 31)))

Paul Newcome

Do you have that error in any cell within any of the ranges?

Are the Start Date and End Date columns both set as date type columns?

How are each of the date type columns being populated?

HuiXian

Hi Paul, im actually trying to do a sheet summary.

Start and End date column is setup as date type.

Paul Newcome

What formula exactly are you using to populate the % column?

HuiXian

im trying to sum up the allocation % that is between the period of time, say between 1 Quarter

Paul Newcome

I understand what you are trying to accomplish here. What I am asking is regarding the data itself. It looks like you have either a formula with a cross sheet reference or a cell link coming into that column.

HuiXian

ahhh sorry i misunderstood.. So the % column is populated within this column formula.

Paul Newcome

Ok. And how is it being calculated on the other sheet?

HuiXian

The other sheet is actually a consolidated sheet (ie it pulls the data from another sheet and summarizes the information, making it a central repository - Sheet 3).

i tried to provide an overview of how the sheets are linked to see if its causing the error observed

-------------

Intent is to create a visual tool to automate resource estimation based on a set of criteria against a timeframe:

#1. A Questionnaire is setup for intake to take in the requirements

#2. Sheet 1: Questionnaire is being scored against an "answer" sheet which will provide the resource level base on the response selection

#3. Sheet 2: Responses from the Questionnaire is extracted to another sheet where it is populated against a timeframe and the resource level is being averaged

#4. Sheet 3: Central repository of all the responses with the averaged resource and this will be come the source of Sheet 4

#5. Sheet 4: Looks up sheet 3 base on the requirements to show the average resource requirements. There is a section for user to populate the project timeline.

Data flow is as above and i actually got into the above error at #5 - Sheet 4

Paul Newcome

That still doesn't tell me exactly how the % is being calculated at the source.