Using Wildcards In Numbers Formulas

You can use the wildcard characters question mark and asterisk in some Numbers functions to get more powerful results. In this example, we'll look at using a SUMIF function that sums rows that match only a portion of a string, rather than an exact match.

You can also watch this video at YouTube (but with ads).

Video Transcript: So let's take a look at using wildcards in Numbers. Wildcards allow you to compare strings but specify that you don't care what a particular character or group of characters is. For instance, say here we've a table with product sales. We've got products with names like A0012, A0016, etc. Say we want to get the total for a certain product or group of products.
So I can do a Sum If formula here. So I'll put a formula here, SUMIF, and then we'll take column A here, so product sales A, and what we'll do is we'll compare that to A0012. I'll put quotes around this and that will return TRUE in two cases here cases here, two rows. We'll say when it's TRUE we'll going to take the values in Column B. The result is nine because we have this first row here is A0012 and it's a 4. This one is A0012 is a 5. That is a total of 9. 
But let's say we wanted to total up all of the products that start with A00. Well, we can do that using the same formula here but instead of 1, 2 we can put question mark, question mark ?? and that means for those characters that are question marks it could be any character. So now when I use the formula you can see I get eighteen. It matches all of those because they all start with A except for the fifth row here. So 4+6+3+5 is 18. It's not including the 7 there.
Now I could make this a little bit better. I could say I don't care what the number is I'm going to put four questions marks ???? instead of the four digits that are there. I could also use the asterisk, which is Shift and then the 8 key on American keyboards. This says any number of characters after it. So if I do that it's going to give me the same result. But it would give me the same result, say, if I mistyped or if there was a product here that was A016. It's only three characters after it. This doesn't matter how many are after it.
So that gives me the ability to basically to kind of do an or type of function here inside of SUMIF. I can get results like give me everything that's got a product name starting with A. Now you can use all sorts of different things with these. You can say asterisk A asterisk which would mean anything that contains the letter A or anything that contains the word apple. You can also do like question mark, question mark so the first two characters don't matter. The next four need to be test and then there needs to be, you know,  just anything after it.
So you can use any combinations of question marks and asterisks to indicate one or many different characters there. You can play around with that. It might be useful. If you ever need to indicate that a character is the real thing, in other words you're actually looking for an asterisk, then what you want to do is use the tilde character, the shift and the key right above the tab. If you use that with an asterisk or question mark it basically says use that exact character.
You can combine this with value themselves. So for instance I can say A here and B here and I can create a SUMIF and do this column. The value I'm looking for is the value here. Then use the ampersand to                              strings and say asterisk. So anything A and then an asterisk, a wildcard after it, and then give me the total here in B. That gives me 18 there. If I copy and paste here it's going to give me 7. This is looking for anything starting with A. This is looking for anything starting with B.
Now you can only use wildcards in certain specific functions. If you do a search in Numbers, Help for wildcard you can find a page that lists functions that accept the conditions as arguments. You can see several of them here. Things like average if, count if, Hlookup, match, search, and Blookup. So there's only a few certain functions that accept this. But if you use those and you need to do this kind of thing it's very useful and very versatile.

Using Wildcards In Numbers Formulas

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information