Convert SUMIFS Lookup Range from Number to Text

[ajones]

I have a formula (simplified example) =SUMIFS( A1:A10 , B1:B10 , 3 & "*")
My problem is B1:B10 are numbers and not text. As 3&"*" will always be text I need to convert the range to text. I would really like to to keep the column itself as text and to do the conversion in the formula. However I can't find any text functions that work on an array even (ctr-shift-enter).

Any ideas or thoughts to something like =SUMIFS( maketext(A1:A10) , B1:B10 , 3 & "*")

This is in Excel 365.

thanks

Alan

 

[jasonb75]

Are the numbers a fixed number of digits or variable?
If fixed, something like this will work.

=SUMIFS(A1:A10,B1:B10,">=30000",B1:B10 ,"<=39999")

 

[ajones]

Are the numbers a fixed number of digits or variable?
If fixed, something like this will work.

=SUMIFS(A1:A10,B1:B10,">=30000",B1:B10 ,"<=39999")

thanks for the response.... interesting idea... unfortunately I may have simplified it to to much as the 3 in "3 & "*" " is really referencing another cell that will vary based on the row.

Something like this

=SUMIFS( maketext(A1:A10) , B1:B10 , D1 & "*")

That makes it harder and why i was waning an array to text type formula.

thanks for the good idea though.

 

[arthurbr]

Perhaps

 =sumproduct(--(left(b1:b10,1)="3"),a1:a10)

Works with text or numbers

 

[jasonb75]

the 3 in "3 & "*" " is really referencing another cell that will vary based on the row.

If the number of digits is fixed then it could still work.

As you said, you may have simplified it too much. The more detail you give us to work with, the better the answer we give you.
For 5 digit numbers this will work and more efficiently than sumproduct on an equal size range.

=SUMIFS(A1:A10,B1:B10,">="&D1&"0000",B1:B10,"<"&D1+1&"0000")

 

[ajones]

If the number of digits is fixed then it could still work.

As you said, you may have simplified it too much. The more detail you give us to work with, the better the answer we give you.
For 5 digit numbers this will work and more efficiently than sumproduct on an equal size range.

=SUMIFS(A1:A10,B1:B10,">="&D1&"0000",B1:B10,"<"&D1+1&"0000")

I had debated about using sumproduct(). I really like sumproduct, but people coming behind me is is harder for them to understand. That and I really like the wild card option in SUMIFS. I wish they would add support for that in SUMPRODUCT().

Thanks for the ideas.