# Calculating a column based on date

Options
✭✭✭✭
edited 12/09/19

I'm trying to total up the job value column based on weekly totals. Can I have the formula calculate the weeknumber or do I have to create a helper column?

Tags:
«1

• ✭✭
Options

Try replacing WEEKNUMBER(30) with WEEKNUMBER(@cell)=30.

• ✭✭✭✭
Options

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, WEEKNUMBER(@CELL)=30)

I'm still getting #UNPARSEABLE

• ✭✭
Options

Try it with @cell in lower case:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, WEEKNUMBER(@cell)=30)

• ✭✭✭✭
Options

Now I'm getting a different error.. sorry, if feel high maintenance for something simple

• ✭✭
edited 08/14/19
Options

Sorry, I forgot that Smartsheet gets confused by blanks in the date column. Try this:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, NOT(ISBLANK(@cell)), {SHIPPED JOBS Range 5}, WEEKNUMBER(@cell) = 30)

• ✭✭✭✭
Options

That did it! Thank you. Always learning:)

• ✭✭✭✭✭✭
Options

There are a few other solutions in addition to Brian's. Not to say that Brian's is wrong or that any of these are "better". Just showing a few other options that will all accomplish the same result in different ways while accounting for different things in different ways.

Brian's:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, NOT(ISBLANK(@cell)), {SHIPPED JOBS Range 5}, WEEKNUMBER(@cell) = 30)

.

Using AND to incorporate both sets of criteria into the same range:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, AND(NOT(ISBLANK(@cell)), WEEKNUMBER(@cell) = 30))

.

Use ISDATE(@cell) instead of NOT(ISBLANK(@cell)). This will prevent an error if a text value happens to be in the Date column:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, AND(ISDATE(@cell), WEEKNUMBER(@cell) = 30))

.

Using an IFERROR to account for non-date values within the WEEKNUMBER function:

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, IFERROR(WEEKNUMBER(@cell), 0) = 30)

.

Using a DATEONLY function (because dates within SUMIFS and COUNTIFS especially in cross sheet references can get VERY particular):

=SUMIFS({SHIPPED JOBS Range 1}, {SHIPPED JOBS Range 5}, WEEKNUMBER(DATEONLY(@cell)) = 30)

.

You may want to also consider exactly what data will be on the source sheet. If there can be an overlap in years, you may also want to account for that by using the YEAR function (or other various methods) to ensure you are pulling for ONLY [weeknumber 30 of 2019] and not [weeknumber 30 of 2019 + weeknumber 30 of 2018].

• ✭✭✭✭
Options

@PaulNewcome

Thank you for the additional help.

I'm trying to apply the same formula to another sheet (and used some of your recommendation) but I'm getting an error.. I'm trying to add the retainer values based on the Weeknumber again

• ✭✭✭✭✭✭
Options

Move one of the closing parenthesis from the end to after your @cell reference.

The way it is written in your screenshot is that you are looking for the weeknumber of DATEONLY(@cell) = 33 when really you are wanting the weeknumber of DATEONLY(@cell) to equal 33 (if that makes sense).

It should be

WEEKNUMBER(DATEONLY(@cell)) = 33)

• ✭✭✭✭
Options

It's still giving me incorrect arugument?

• ✭✭✭✭✭✭
Options

It could be your ranges. The range to sum should be first, then your date range. Also make sure the ranges are the same size.

• ✭✭✭✭
Options

The Range 4 is the Product Value Column and the Range 5 is the Retainer Date Column

I selected the entire column for both

• ✭✭✭✭✭✭
Options

Are there blanks or non-date values in your date column? Try wrapping your WEEKNUMBER function in an IFERROR to replace blanks and text values with a zero. The DATEONLY function SHOULD be taking care of that, but just to be sure.

IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = 33

• ✭✭✭✭
Options

Yes! that did it. Thank you for your help

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!