Sumifs with Less Than for Numbers entered as Values

[MikeDBMan]

I have data from an ERP System. A column contains numbers but they all arrive from the ERP system as text. In other words, 300000 is '300000, etc. I need to do this:

Sumifs(J:J,I:I,"<" &"400000") . Col J has the values to add up.
Col I contains the locations as string, "300000", "350000", "400000", etc.

I only want the sumifs formula to add up J when I is < "400000" but it continually returns Zero? Ideas?

Sure, I could add a column and make it the values of Column I but I don't want to have to add that overhead.

 

[Special-K99]

Does this work?

Sumifs(TEXT(J:J,"000000")+0,TEXT(I:I,"000000")+0,"<" &"400000")

 

[Special-K99]

No it doesn't but this should:

=SUMPRODUCT((TEXT(I1:I100,"000000")+0<400000)*(TEXT(J1:J100,"000000")+0))

 

[MikeDBMan]

That works great. Thank you so much!