Asterick "*" Causing incorrect summation in an array formula - any advice?

[jchenry]

I am using a sumif array formula:
=SUM(SUMIFS($T$6:$T$3000,$D$6:$D$3000,$B3,$B$6:$B$3000,"Y",$BJ$6:$BJ$3000,{"1,2,3","1,3","2,3",3}))

this adds items that have = {"1,2,3","1,3","2,3",3} these different combinations. I realized there is also another option I must consider "2*,3" however when I add this to the formula {"1,2,3","1,3","2,3",3,"2*,3"} the output is incorrect. If I change the "2*,3 value to "4" for instance ({"1,2,3","1,3","2,3",3,4}) it works perfectly.

I assume there is some error caused by the "*" but I cannot control the inputs of this cell so I need to be able to find a text value with the "*" in it to compare.

Any help is appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

The asterisk is acting as a wildcard. Try changing "2*,3" to "2~*,3"

 

[jchenry]

Peter - Thank you very much! That worked of course. I feel very silly, but I can now sleep easier.

 

[Peter_SSs]

Peter - Thank you very much! That worked of course. I feel very silly, but I can now sleep easier.

Cheers. No need to feel silly, this is what the forum is for. Enjoy your sleep.