# Sum up values if it falls within a period

Options

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)))

Cheers

HuiXian

• ✭✭✭✭✭✭
Options

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

• Options

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)))

• ✭✭✭✭✭✭
Options

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?

• Options

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

Start and End date column is setup as date type.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!