# Having a cell reference for SUMIFS that should work as a wildcard

Hi!

I have a formula that looks up sales-data. We sort everything in categories (1-5) and then groups (01-99) and then models (01-99) and I use a SUMIFS to sort the data. Today my formula looks like this if I want to get info from every model in category 4 group 87:

=SUMIFS(\$‘137’.\$G\$2:\$G\$100000;\$‘137’.\$D\$2:\$D\$100000;\$A5;\$‘137’.\$M\$2:\$M\$100000;"=487.*")

I would like to change the last part to reference to a cell instead so I could change search criteria quick and easy, and it works if I insert the full CGM type in the reference cell (48710 for example if I want to look up that specific model) but if I want everything in 487 the wildcard doesn’t work. I tried .* in the cell and .* in the formula, like this:

=SUMIFS(\$‘137’.\$G\$2:\$G\$100000;\$‘137’.\$D\$2:\$D\$100000;\$A5;\$‘137’.\$M\$2:\$M\$100000;"=\$E\$2.*")

The reference doesn’t work inside quotes, please test with:

``=SUMIFS(\$'137'.\$G\$2:\$G\$100000;\$'137'.\$D\$2:\$D\$100000;\$A5;\$'137'.\$M\$2:\$M\$100000;\$E\$2&".*")``

The text coming from cell E2 needs to be concatenated with short text constants to get the applicable criterion (as RegEx). It should work this way. The `"="&` should be dispensable as “equal” is the default comparator for criteria.
`=SUMIFS(\$'137'.\$G\$2:\$G\$100000;\$'137'.\$D\$2:\$D\$100000;\$A5;\$'137'.\$M\$2:\$M\$100000;"="&\$E\$2&".*")`

Don’t use numbers (“numeric text”) as sheet names to get rid of the annoying apostrophes in the references.