CODEWITHSUNDEEP
excelgroupingwildcardsumifs
sjbka
sjbka

Reputation: 45

Excel - numeric wildcard to aid grouping of values?

I'm using SUMIFS in Excel to add values on the basis of a site name. Currently, I'm using a * wildcard to establish a generic site name so that similar site names are considered together (e.g. London&'*' to group together "London 1" and "London 2"). My problem is that when the site names are too similar, as below:

"London 1"
"London 2"
"London Oxford St 1"
"London Oxford St 2"
"London Hammersmith"

then London&'*' considers all of the above site names together, instead of grouping the ones that I want to group together (i.e. the ones where the site name is followed by a numeric value; "London 1" and "London 2").

Is there a way of considering only numeric values for the wildcard instead of any text?

Upvotes: 2

Views: 312

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34210

You could also try this if you definitely wanted ones ending in a number to be grouped (works for a single digit)

=SUMPRODUCT(ISNUMBER(FIND("London "&{1,2,3,4,5,6,7,8,9,0},A2:A10))*B2:B10)

where the site names are in column A and the values in column B.

EDIT

This is actually equivalent to "*London [0-9]*" so "Baker Street " would also find Sherlock Holmes Baker Street 221B.

This is equivalent to "London [0-9]"

=SUMPRODUCT((A2:A10="London "&{1,2,3,4,5,6,7,8,9,0})*B2:B10)

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

For London 1 and London 2 you can use the question mark ? wildcard

London ?

It refers to a single character, rather than any number of characters - which is what the asterisk gives you

Note that it won't give you anything greater than London 9. For London 10 to London 99 you would need to have London ?? as your criteria

Also note that I didn't use the ampersand &. "London"&"*" is the same as "London*"

Upvotes: 2

Related Questions