Sheet Summary SUMIF Function Problem
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])
Thanks
Best Answer

Sometimes you can receive a #BLOCKED error if either of the referenced columns have an error in them as well. Can you check through both PIERCE FISCAL YEAR SOLD and As Ordered Dealer Net Amount to see if there's an error in either of these columns?
If you're using a formula in either of these columns and there's an error, you can use the IFERROR function to return text instead of an error, which then means your SUMIF formula can then reference the column and skip over those cells.
Let me know if this makes sense!
Cheers,
Genevieve
Answers

You should be able to remove your extra closed parenthesis [PIERCE FISCAL YEAR SOLD]
),"FY2021"...so give this a try:=SUMIF([PIERCE FISCAL YEAR SOLD]:[PIERCE FISCAL YEAR SOLD],"FY2021",[As Ordered Dealer Net Amount]:[As Ordered Dealer Net Amount])

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!

Sometimes you can receive a #BLOCKED error if either of the referenced columns have an error in them as well. Can you check through both PIERCE FISCAL YEAR SOLD and As Ordered Dealer Net Amount to see if there's an error in either of these columns?
If you're using a formula in either of these columns and there's an error, you can use the IFERROR function to return text instead of an error, which then means your SUMIF formula can then reference the column and skip over those cells.
Let me know if this makes sense!
Cheers,
Genevieve

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!

Glad you got it working! 🙂
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!