Problem with SUMIF or SUMIFS

Options

I'm trying to sort and total by month and year, 14 different items. All the data is from a different sheet. I can get the grand totals I want by using SUMIF as shown in the screenshot, but whatever I try to do I cant get a formula to work that would give me totals by month and year, in this case for the next four columns.

Any help would be greatly appreciated. Btw, I hadn't ever used Smartsheets until about 10 days ago. Absolutely love it and what it can do.

Thank you in advance for any help.


Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Good afternoon @Chris Benskin ,

    I tested the formula below and it works for me. I added "@cell=" to the 1st range criterion.

    =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM5002 Rev. B Range 1}, @cell=[Primary Column]@row, {Purchasing Log_FM0052 Rev. B Range 3}, Month(@cell)=11, {Purchasing Log_FM0052 Rev. B Range 3}, Year(@cell)=2020)

    Confirm that your PO Date column is a date column. internal Cost Center and Subtotal should be text/number columns.

    We got this.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Chris Benskin ,

    A couple suggestions:

    If all the formulas in your total column are using the same range references, you can change the SUMIF formula so that the criteria is [Primary Column]@row instead of the embedded text. That way you can convert it to a column formula which is more efficient.

    To get totals by month enter this formula in the month columns:

    =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM5002 Rev. B Range 1}, =[Primary Column]@row, {Insert range with dates}, Month(@cell)=11, {Insert range with dates}, Year(@cell)=2020). Where {Insert range with dates} is the external sheet reference to the date data.

    Insert that formula into the other date columns and change the Month(@cell)=XX to the month you want totaled.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Chris Benskin
    Options

    I get "#UNPARSEABLE".

    The Primary Column does contain some blanks. Is this what is causing the "#UNPARSEABLE"?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Chris Benskin ,

    Blanks shouldn't cause this error. #UNPARSEABLE happens with a formula problem; such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

    The formula you entered was missing a } and had a couple typos. Try:

    =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM5002 Rev. B Range 1}, [Primary Column]@row, {Purchasing Log_FM0052 Rev. B Range 3}, Month(@cell)=11, {Purchasing Log_FM0052 Rev. B Range 3}, Year(@cell)=2020)

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Chris Benskin
    Options

    I'm still having problems. Tomorrow I will take some screen shots to share, maybe that exercise will help me clarify what I'm trying to do. Seems so simple but I'm missing something.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Happy to help you find a solution. Screenshots would be helpful.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Chris Benskin
    Options

    Mark

    Here is what we have been working on. Trying to get monthly totals for the associated Internal Cost Centers which are the Primary Column.

    Here is the Purchasing Log Sheet that has the information. I have written the corresponding range numbers on the appropriate column.


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Good afternoon @Chris Benskin ,

    I tested the formula below and it works for me. I added "@cell=" to the 1st range criterion.

    =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM5002 Rev. B Range 1}, @cell=[Primary Column]@row, {Purchasing Log_FM0052 Rev. B Range 3}, Month(@cell)=11, {Purchasing Log_FM0052 Rev. B Range 3}, Year(@cell)=2020)

    Confirm that your PO Date column is a date column. internal Cost Center and Subtotal should be text/number columns.

    We got this.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Chris Benskin
    Options

    Mark

    You got it! Thank you for the assistance in getting this working right. This is a type of sheet and formula that I will use frequently in my day to day activities.

    Thank you again.

    Chris

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Chris,

    Happy to help. Thanks for using the Community.

    Be Well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!