# Excel Sumifs result changes when using "="& before criteria

#### CRHAAG

##### New Member
Hi all,

I noticed a strange occurrence with the SUMIFS formula today and am wondering if someone might just be able to explain to me why this is. Lets say I have the following two formulas:

SUMIFS(FIELD_AUDIT[PRP QTY],FIELD_AUDIT[BLD],reportBuildingCode,FIELD_AUDIT[PRP TYPE],[@[Prp Type]],FIELD_AUDIT[EXST TYPE],[@[Exst Type]])))
SUMIFS(FIELD_AUDIT[PRP QTY],FIELD_AUDIT[BLD],reportBuildingCode,FIELD_AUDIT[PRP TYPE],[@[Prp Type]],FIELD_AUDIT[EXST TYPE],"="&[@[Exst Type]])))

The only thing different in the second one is that it has "="& before the criteria, rather than just stating the criteria. I have always used "="& but learned recently that this is not necessary when you simply want to sum if the value in your range is equal to your criteria. I noticed a value coming up blank today that I knew should not have. After racking my brain for 20 minutes or so, I decided to try rewriting the formula with "="&. To my surprise it worked.

Can anybody tell me if there is a functional difference between these two formulas?

Thanks

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

##### Well-known Member
if you have equal sign also within cells at range you should use second formula &
If Not you should use first, I think.

#### CRHAAG

##### New Member
Are you saying that if FIELD_AUDIT[PRP QTY] is a formula I would want to use the first one? It is not a formula. It does have a validation dropdown that limits values to whole numbers....

Replies
4
Views
436
Replies
11
Views
476
Replies
10
Views
312
Replies
2
Views
93
Replies
6
Views
258

1,140,917
Messages
5,703,169
Members
421,279
Latest member
emzy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back