2

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?

3N1GM4
  • 3,372
  • 3
  • 19
  • 40
sjbka
  • 45
  • 5

2 Answers2

2

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

CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • Happy to help, if this answered your question please accept the answer by clicking the tick mark to the left of this answer – CallumDA Jan 13 '17 at 11:33
0

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)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37