How to remove @ in sumif formula

Question

I have the following formula to compare invoice numbers of one sheet to the other and pull the values from the one sheet to the other sheet

=SUMIF(Sheet1!B:B,Sheet!C:C,Sheet1!M:M)

This formula works on everyone's machine except on 1 person's machine. It returns '0' values.

For instance when opening copying the same document to another persons machine and clicking on the cell where the formula is used, it works with no issues

When trying to create a new document the same results apply

When opening other documents where the formula was used previously, I noticed an @ sign to the formula. When using the @ in the formula as below the correct values are displayed.

=SUMIF(Sheet1!B:B,@Sheet!C:C,Sheet1!M:M)

This did not happen before, the formula worked without the @ sign previously. How do I get Excel to remove the @ sign/formula to work without the @ sign.

I have been researching, but cannot find solutions. I am not sure if it is just a setting in Excel

On other machines the @ sign does not display

No updates were installed on the machine

Uninstalled Office 365 and Installed again. No change. This seems to machine/Excel specific. 

Any assistance will be great 

Thanks

Answer 1

Hi Blush0508,

I go through the post. The implicit intersection operator @ was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays. As you can see in this article: Implicit intersection operator: @

“Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value.” Since it is an upgrade formula language, you may not see the symbol in Excel with version that doesn’t support Dynamic Array.

The formula you shared with us indicates Criteria part of the SUMIF function is array type because it references data in a whole column. It could be a reason why there is @.

If you compare invoice number in the same row in two sheets, I think you can change the formula to:

=IF(Sheet1!B:B=Sheet!C:C,Sheet1!M:M,”Not Found”).

Because the behavior is related to Excel of different Version. I would suggest you check if the person has different version of Excel from others.

Regards,

Clark

Answer 2

Hi,

The second input of the SUMIF() function should be a single cell criteria or a hard coded value.  So in cell E2, enter this formula and copy down

=SUMIF(Sheet1!B:B,C2,Sheet1!M:M)

This should now work on all machines.

Answer 3

Hi Blush0508,

Does information above help you out? Welcome to share any updates here with us!

Regards,

Clark