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

[CRHAAG]

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

 

[maabadi]

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

 

[CRHAAG]

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....