metric sheet and sumif formula

Options

Hi I am having a tough time with a sumif formula on a metric sheet.

=SUMIFS({Location # and Name 4}, LOCATION@row, {AP $ AMT POSTED})

I am wanting to count by location the $ in the AP $ AMT POSTED column. Any help is appreciated.


Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Melanie Sanders ,

    I noticed that you have ", <>" at the end of your formula in the screenshot. You'll definitely want to take that out, as that's likely causing the #unparseable error. Additionally, if you are only using one criteria, you can use SUMIF. SUMIF and SUMIFS are different in that the range you want summed changes location within the formula:

    =SUMIFS({range to sum}, criteria_range1, criteria_1, criteria_range2, criteria_2)

    =SUMIF(criteria_range, criteria, {range to sum})


    So, your formula would need to either be:

    =SUMIF({Location # and Name 4}, LOCATION@row, {AP $ AMT POSTED})

    or

    =SUMIFS({AP $ AMT POSTED}, {Location # and Name 4}, LOCATION@row)


    Hope this helps! Let me know if it works for you.

    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Melanie Sanders ,

    I noticed that you have ", <>" at the end of your formula in the screenshot. You'll definitely want to take that out, as that's likely causing the #unparseable error. Additionally, if you are only using one criteria, you can use SUMIF. SUMIF and SUMIFS are different in that the range you want summed changes location within the formula:

    =SUMIFS({range to sum}, criteria_range1, criteria_1, criteria_range2, criteria_2)

    =SUMIF(criteria_range, criteria, {range to sum})


    So, your formula would need to either be:

    =SUMIF({Location # and Name 4}, LOCATION@row, {AP $ AMT POSTED})

    or

    =SUMIFS({AP $ AMT POSTED}, {Location # and Name 4}, LOCATION@row)


    Hope this helps! Let me know if it works for you.

    Best,

    Heather

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭
    Options

    @Heather Duff

    Thank you for that....sometimes my brain has a fog. If I wanted to subtract from the AP$ AMT Posted what would the next part of my formula be?

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @Melanie Sanders Can you elaborate on what you want to subtract from the AP$ AMT Posted?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!