# Sheet Summary SUMIF Function Problem

Options
edited 11/18/20

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

• ✭✭✭✭
Options

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

• Options

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.

• Options

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!