Sheet Summary SUMIF Function Problem

David Klockzien
edited 11/18/20 in Formulas and Functions

I have a sheet called Order Execution with many columns for data.

[As Ordered Dealer Net Amount] is a text column for inputting sales financials

[Pierce Fiscal year Sold] is a single select list column with FY2021 etc. for choices.

I want to add a sheet summary formula that will SUM all As Ordered Dealer Net Amounts where the same row has "FY2021" in the Pierce Fiscal Year Sold Column. I have tried many different formulas including SUMIF, IF and SUM etc. The formula below is my last chance but when run it states #BLOCKED

I put my data into excel and typed in the same formula except I replaced the Column References with A:A and B:B respectively and I get a perfect sum. Can someone tell me how to fix this?

=SUMIF([PIERCE FISCAL YEAR SOLD]:[PIERCE FISCAL YEAR SOLD]),"FY2021",[As Ordered Dealer Net Amount]:[As Ordered Dealer Net Amount])


Best Answer


  • John Jonassen
    John Jonassen ✭✭✭✭

    Hi @David Klockzien

    You should be able to remove your extra closed parenthesis [PIERCE FISCAL YEAR SOLD]),"FY2021" give this a try:

    =SUMIF([PIERCE FISCAL YEAR SOLD]:[PIERCE FISCAL YEAR SOLD],"FY2021",[As Ordered Dealer Net Amount]:[As Ordered Dealer Net Amount])

  • David Klockzien

    Hi John,

    I removed the extra closed parenthesis but I still get the #BLOCKED error. Is it causing a circular reference? If so how would I resolve that? As I mentioned, if I run this exact equation in excel substituting A:A and B:B for the column names, it works great.

    Thanks for your help!

  • David Klockzien

    Thanks everyone for the help. I ended up moving all me metrics to another sheet and that solved the issue. Must be a circular reference issue if the formula was in the sheet summary. Working now!! Thanks again!

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad you got it working! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!