SUMIF without Formatting Column A as Text

[EnigmaMatter]

Hello,

I would like to sum all values in Column B that have a number starting with 115 in Column A. However, I cannot format the column as TEXT.

See example workbook.

[D.Lovell]

Hi There,

Maybe like this? ....

Please Login or Register  to view this content.

[sandy666]

try:

Please Login or Register  to view this content.

[JohnTopley]

Try

=SUMPRODUCT(--(INT($A$2:$A$4/(10^(LEN($A$2:$A$4)-3)))=115)*($B$2:$B$4))

allows for 115876 i.e numbers of length >= 4

[MarvinP]

Hi EnigmaMatter,

How about using a Helper Column that converts the numbers to text and then do your sumif?

See the attached.

[EnigmaMatter]

Try

=SUMPRODUCT(--(INT($A$2:$A$4/(10^(LEN($A$2:$A$4)-3)))=115)*($B$2:$B$4))

allows for 115876 i.e numbers of length >= 4

This is the winning answer. Thabks everyone.

[José Augusto]

Hi

Try this solution

=SUMPRODUCT(--(INT((INT(A2:A4)/10^(INT(LOG(A2:A4,10))-2)))=115),B2:B4)

for any numbers and no text.
Regards

[sandy666]

Please Login or Register  to view this content.

tested with a long string, text and/or numbers

[JohnTopley]

@Sandy666 best solution! I am sure I tried that solution but it didn't (?) work! (too late in the day I think)

[sandy666]

thank you John