how to get sum of a column to include only certain date range

Joanne Devenish

Please could someone help me with this?

We have recently upgraded to be able to use the summary sheet as well as automated workflows, but neither of these have helped, and I think it maybe a simple formula issue?

Basically, we use smartsheet for customers to place preorders of different products (approx 50 different columns for products), and the orders come in onto new rows. WE then need to total these rows using the sum function. We currently update the sum to include recently added rows, but I would like to automate this so that it automatically calculates the totals whenever a new row is added.

I have tried something similar to the following formula =sumif(date column:date column, MONTH=11, [Product1:Product1]), but it keeps saying 'unsurpasseable' and I don't know why.

POssibly because the sheet uses a lot of parent and child rows?

Hope this makes sense. Would be really grateful of some help on this.

L_123

=sumif([date column]:[date column], iferror(MONTH(@cell)=11,0), [Product1]:[Product1])

No, it's most likely because it is running the month formula on blank cell, an oversight on my part. Try the above with the iferror in it

Genevieve P.

Hi @Joanne Devenish

Would it be possible to see a screen capture of your sheet & column names (but please block out any sensitive data!).

This type of error indicates there's some sort of typo, whether that's an incorrect element placed somewhere like an extra comma, or a column name spelled or referenced incorrectly.

=sumif([date column]:[date column], MONTH(@cell)=11, [Product1]:[Product1])

^ this formula should work, but only if the following is true:

• Your date column is titled exactly "Date Column"
• The date column is set up as a date Type of column
• Your column to SUM is titled exactly "Product1"

Column titles need to be exact, including spaces. Capitals don't matter, but spaces and extra letters do.

I would also suggest wrapping an IFERROR function around your MONTH portion of the formula, just in case it's throwing the error if it can't find the correct month or it's trying to read blank cells.

Try this:

=SUMIF([date column]:[date column], IFERROR(MONTH(@cell), 0) =11, [Product1]:[Product1])

Cheers!

Genevieve

Genevieve P.

Hi @Joanne Devenish

Yes, no problem! So instead of looking for a MONTH as your criteria, you could either use the DATE function (see here) to search for a specific date or between two dates, or you could use the TODAY function to look for a date within the range of TODAY.

Ex:

=sumif([date column]:[date column], TODAY(), [Product1]:[Product1])

^ This will look for just the one date, Today's date.

=SUMIFS([Product1]:[Product1], [date column]:[date column], >=TODAY(), [date column]:[date column], <=TODAY(7))

^This looks for a range... greater-than or equal-to Today, and less-than or equal-to 7 days from Today. Note that with SUMIFS (plural), you add the range to SUM as the first range instead of the last.

To help further we'd need to know a bit more information and the specific criteria you're looking for.

🙂

Cheers!

Genevieve

L_123

=sumif([date column]:[date column], MONTH(@cell)=11, [Product1]:[Product1])

Joanne Devenish

Thank you. Unfortunately that still doesn't seem to work, and says ' Unpasseable'. Could this be due to the parent/child rows in the sheet?

L_123

=sumif([date column]:[date column], iferror(MONTH(@cell)=11,0), [Product1]:[Product1])

No, it's most likely because it is running the month formula on blank cell, an oversight on my part. Try the above with the iferror in it

Genevieve P.

Hi @Joanne Devenish

Would it be possible to see a screen capture of your sheet & column names (but please block out any sensitive data!).

This type of error indicates there's some sort of typo, whether that's an incorrect element placed somewhere like an extra comma, or a column name spelled or referenced incorrectly.

=sumif([date column]:[date column], MONTH(@cell)=11, [Product1]:[Product1])

^ this formula should work, but only if the following is true:

• Your date column is titled exactly "Date Column"
• The date column is set up as a date Type of column
• Your column to SUM is titled exactly "Product1"

Column titles need to be exact, including spaces. Capitals don't matter, but spaces and extra letters do.

I would also suggest wrapping an IFERROR function around your MONTH portion of the formula, just in case it's throwing the error if it can't find the correct month or it's trying to read blank cells.

Try this:

=SUMIF([date column]:[date column], IFERROR(MONTH(@cell), 0) =11, [Product1]:[Product1])

Cheers!

Genevieve

Joanne Devenish

Thank you Genevieve, that worked!

Do you know if there is a way to refine even further, to only include certain dates? or just for the past week?

This would be ideal, but not sure if that is past the 'formula' stage really. :) am working on another idea which is a separate sheet for weekly orders, and then an automated workflow to move the recent orders to an archive sheet.

Genevieve P.

Hi @Joanne Devenish

Yes, no problem! So instead of looking for a MONTH as your criteria, you could either use the DATE function (see here) to search for a specific date or between two dates, or you could use the TODAY function to look for a date within the range of TODAY.

Ex:

=sumif([date column]:[date column], TODAY(), [Product1]:[Product1])

^ This will look for just the one date, Today's date.

=SUMIFS([Product1]:[Product1], [date column]:[date column], >=TODAY(), [date column]:[date column], <=TODAY(7))

^This looks for a range... greater-than or equal-to Today, and less-than or equal-to 7 days from Today. Note that with SUMIFS (plural), you add the range to SUM as the first range instead of the last.

To help further we'd need to know a bit more information and the specific criteria you're looking for.

🙂

Cheers!

Genevieve

Joanne Devenish

Excellent, that is very helpful. Thank you for your help! :)

Genevieve P.

No problem at all! If you have a specific range or date you're looking for and you need help with the formula, let us know and we'd be happy to help further. 🙂