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

[helgesson88]

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.*")

[mariosv]

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&".*")

[Lupp]

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.