Sheet Summary SUMIF Function Problem

David Klockzien

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

Genevieve P.

Hi @David Klockzien

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

John Jonassen

Hi @David Klockzien

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])

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!

Genevieve P.

Hi @David Klockzien

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

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.

Glad you got it working! 🙂