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

CRHAAG

New Member
Joined
Jul 3, 2014
Messages
45
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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,294
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Jul 3, 2014
Messages
45
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....
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top