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

edit retag close merge delete

Sort by » oldest newest most voted

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.

more

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

more

## Stats

Asked: 2017-07-11 23:06:06 +0200

Seen: 175 times

Last updated: Jul 11 '17