Ask Your Question
0

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

asked 2017-07-11 23:06:06 +0100

helgesson88 gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-07-11 23:20:35 +0100

Lupp gravatar image

updated 2017-07-11 23:21:42 +0100

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.

edit flag offensive delete link more
0

answered 2017-07-11 23:12:22 +0100

m.a.riosv gravatar image

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&".*")
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 132 times

Last updated: Jul 11 '17