Last week Ann emailed me a question and before I had a chance to even look at it she emailed me again with her solution. I like those kind of questions 😉
This is something I’ve never seen before and it’s quite clever so I thought I’d share it with you.
Q: How can I sum data in column C for all names in column B beginning with A, B and C?
I never knew you could use greater than or less than operators with letters!
I had a play around with this technique and there are a couple of quirks with it.
I’ve set up some mock data below which I'll use in my examples.
Note: The Grade range B2:B13 has a named range ‘grade’ and the Score range C2:C13 has a named range ‘score’ which you will see in my formulas below:
Using the table below we want to sum various columns filtering on the first letter of the product code.
Note: Again I have named the Product range 'prod', the Qty range 'qty' and the Price range 'price'.
Initially I expected to need to use the wildcard symbol * to ignore the remaining characters in the product code, as Ann did in her answer, but it turns out you don't need to.
The formulas below calculate correctly without the need for the wildcard.
In formula number 2 the criteria includes a wildcard “<B*”. This wildcard results in Excel counting both B and A grades.
However if you try to use the wildcard with a greater than operator like this “>B*” it only counts grades from C onward and excludes B. Strange huh?
UPDATE: click here to understand why wildcards work differently when testing strings using > or <.
Thanks to Ann for sharing her trick with me. If you liked this please share it by using the buttons below to Like it on Facebook, Tweet about it on Twitter, share it on Google+ or LinkedIn, or just leave a comment thanking Ann.