Forum Discussion

Copper Contributor

Mar 29, 2019

Solved

SUM.IF

Hi team, I'm using sum.if formula on two different tables. The criteria to sum up amounts is the list of numbers here below. 5806524220010000003340946 5806524220010000016288417 58065242200100000...

excel

Formulas and Functions

SergeiBaklan
Mar 29, 2019
rc14354 , I see. The workaround is to use SUMRODUCT

Please check in attached file
sumif long number texts.xlsx11 KB

Detlef_Lewin

Silver Contributor

Mar 29, 2019

rc14354

Time to bring up this little piece:

The Countif bug (and how to avoid it)

lori_m
Iron Contributor
Mar 29, 2019
Detlef_Lewin Hey just noticed this link! Indeed for numeric text you can use:

=SUMIF(range, CHAR(173)&criteria, [sum_range])

Glad others have picked up on this and it's been put to good use :)
- Detlef_Lewin
  Silver Contributor
  Mar 29, 2019
  lori_m
  Ah! The discoverer herself.
  Solving an eleven year old problem which annoyed almost every Excel user is always worth mentioning.
SergeiBaklan
Diamond Contributor
Mar 29, 2019
Oh, yes, I totally forgot about magic of the CHAR(173). Thank you, Detlef_Lewin