SUMIFS for a date range

Options

I’m in sheet name “Summary” and trying to get total “Cost” from Sheet name “Expenses” where “Charge Date” in sheet “Expenses” between 10/1/2023 and 12/31/2023 -

I’m getting Unparseable error. The formula I'm using is:

 =SUMIFS({Expenses Range 1},[ Charge Date]:[ Charge Date], >=DATE(2023, 10, 1) ,[ Charge Date]:[ Charge Date], <=DATE(2023, 12, 31))

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi

    It looks like you are adding the formula to a sheet called Summary and all the data is in the Expenses sheet. You therefore need to set up cross-sheet references for both columns in the Expenses sheet.

    You have one for the amounts and you have called this {Expenses Range 1}

    You need another for the Charge Date column.

    To do this...

    Edit your formula and highlight the column reference:

    Click on Reference Another Sheet to replace this with a new column reference.

    Find your Expenses sheet in the tree.

    Highlight the Charge Date column.

    Give it a name (or leave it as the Expenses Range 2 default).

    The formula will update like this

    Copy that reference (the one with the arrow) and paste it over the second column reference (highlighted in blue).

    Your formula now looks like this


  • oboutros
    oboutros ✭✭✭
    Answer ✓
    Options

    Fantastic - Worked perfectly. Thank you so much for the quick help

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi

    It looks like you are adding the formula to a sheet called Summary and all the data is in the Expenses sheet. You therefore need to set up cross-sheet references for both columns in the Expenses sheet.

    You have one for the amounts and you have called this {Expenses Range 1}

    You need another for the Charge Date column.

    To do this...

    Edit your formula and highlight the column reference:

    Click on Reference Another Sheet to replace this with a new column reference.

    Find your Expenses sheet in the tree.

    Highlight the Charge Date column.

    Give it a name (or leave it as the Expenses Range 2 default).

    The formula will update like this

    Copy that reference (the one with the arrow) and paste it over the second column reference (highlighted in blue).

    Your formula now looks like this


  • oboutros
    oboutros ✭✭✭
    Answer ✓
    Options

    Fantastic - Worked perfectly. Thank you so much for the quick help

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!