Excel: Dynamic reference in SUMIF formula HELP!!

[Bird222]

I have a list that has items in it that are numbers and text that has numbers in it. If I do this formula

=MATCH("*555*",$D$5:$DW$5),0)

it will find the item that is text, but not the number '555'. Is there someway I can make this work even if it encounters the actual number '555'? Basically, if it is the number 555 or it 'looks like' 555 give a result.

EDIT: New formula problem below. Didn't want to start another thread.

 

[TwiceOver]

Convert the column to text first?

 

[Homerboy]

Wrong forum

 

[rcpratt]

Yeah, just convert the numbers to text. You can't do wildcards with numbers.

 

[Bird222]

I am so close with this formula but I have hit a road block that is driving me nuts. Here is the formula

=SUMIF(INDIRECT("'"&(YEAR($B68)+1)&"-"&(YEAR($B68)+2)&"'!E52:E67"),"="&$C$1,INDIRECT("'"&(YEAR($B68)+1)&"-"&(YEAR($B68)+2)&"'!D52"):INDEX(D1:D85,67))

This simplifies to this

=SUMIF('2019-2020'!E52:E67,"=440",[u]'2019-2020'!$D$52:$D$67[/u])

The underlined portion is the problem. Excel is trying to evaluate it and returns #VALUE. I just need it to think of that as a reference for the SUMIF function. I have tried all kinds of quotes, parentheses, nested INDIRECT functions, etc. I am about to pull my hair out with this. The problem seems to be tied to the INDEX function. Is this possible to do? I am using INDEX plus MATCH functions to get a cell reference. BTW the MATCH function evaluates to the '67' in the original formula. I just omitted it to simplify the formula. Is there a better way find a cell reference from a match?

 

[Tweak155]

This is why I love VBA.

 

[edro]

Oh have you not heard?