Problem with SUMIF or SUMIFS

Chris Benskin

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.

Mark Cronk

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

Mark Cronk

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

Chris Benskin

I get "#UNPARSEABLE".

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

Mark Cronk

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

Chris Benskin

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

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

Mark

Chris Benskin

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

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

Chris Benskin

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

Chris,

Happy to help. Thanks for using the Community.

Be Well,

Mark