Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like this:
How would you go about it?
If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?
Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.
Finding all cells with 7 in them
Assuming the licenses are in column [licenses], you can use below formula to check if it has 7:
Step 1: Make an extra column, say [Two more commas] and use this formula.
=", "&[@licenses]&", "
Step 2: Now use below formula to find if a license has 7 in it:
=COUNTIFS([@[Two more commas]] , "* 7,*") > 0
This formula returns TRUE if [@licenses] has 7.
So how does it work?
There are three cases for licenses with 7 in them, as shown below.
Once we prefix & suffix COMMASPACE to this, we end up with a text that has the pattern:
<COMMA SPACE number><COMMA SPACE number>...
Now, we simply look for the pattern SPACE7, in this by using the * wildcard along with COUNTIFS.
=COUNTIFS([@[Two more commas]], "* 7,*")
We add a check to see if the count is greater than ZERO (ie did we find the pattern?)
So there you go. Now you can find the agent who can nab the targets.
Related: Using wildcards * ? in Excel VLOOKUP & other functions | Introduction to SUMIFS formula
Adding a few plot twists
Now, your MI5 career would be awfully boring, if there are no plot twists. So Q calls you in to her office and says, “We need a list of all agents who have any of the licenses 7, 65 or 63. Oh, while you are at it, tell me which agents have all three licenses.”
Damn you Q
the evil is you
for making me do
work I don’t want to
Damn you Q.
Added later: Okay, My James Bond knowledge is not very good. M is the boss of MI5, not Q. So let’s assume M calls you in to her office and gives you this task. As usual, you go:
Why do this M?
Everything is ho hum
and then you come
to tell my work is not yumm
Why do this M?
So you are back to your desk. Now the licenses to find are in a named range called list.
You can use COUNTIFS() pattern find logic to get the answer.
For sake of simplicity, let’s assume that you have a new column in your data table called as [Two more commas]
Does the agent have any of the licenses in list?
=SUMPRODUCT(COUNTIFS([@[Two more commas]],"* "&list&",*"))>0
The internal COUNTIFS returns an array of values, which the SUMPRODUCT simply adds up.
Does the agent have all of the licenses in list?
=SUMPRODUCT(COUNTIFS([@[Two more commas]],"* "&list&",*")) = COUNTA(list)
Now, let’s hope Q doesn’t add more plot twists. And if she does, you can always post them in the comments so internet can solve them.
Related: Introduction to Excel SUMPRODUCT function
How would you find license to kill?
I am sure COUNTIFS is not the only way to do this. So what would you do in this case? Will you use formulas / VBA or Power Query? Or something else altogether? Go ahead and share your approach in the comments section.
Note: Thanks to Brian who emailed me this problem.
15 Responses to “Christmas Gift List – Set your budget and track gifts using Excel”
[...] Christmas Gift List – Set your budget and track gifts using Excel … [...]
I'm confused: if you spend $10, and your budget is $40, shouldn't the amount in the "Within Budget?" column stay black, since you didn't go over budget?
In other words, since we overspent on the electronic photo frame, shouldn't the $8 cell turn red?
@JP.. maybe Steven is encouraging consumerism... ?
I havent realized it earlier, but now I see it. If you unprotect the sheet, you can change the formula in Column I to =IF(G13=0;" ";F13-G13) from =IF(G13=0;" ";G13-F13), that should correct the behavior.
Thanks Chandoo. I thought of making a shopping list spreadsheet for Christmas, but this is neat so I think I'll use this instead.
Chandoo & Steven thanks for this spreadsheet. But for the sake of a person who has been staring at this megaformula in vain for the last 40 mins and not afraid to ask, would it be possible for you to walk us through the logic used here?
=SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="-"))+SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="0")))&" / "&SUBTOTAL(2,$G$13:$G$62)
Thanks Chandoo.. This is one of the best budget spreadsheets I've ever seen.. The Arrays are out of this world!! And it's FREE!!
Chandoo, can you tell us more about Steven? Does he have his own site?
JP, I think Chandoo changed it when he changed the currency formatting from £ to $, a negative figure is a good thing in this case. But don't change the formulas, the overbudget and under budget won't work properly if you do. Also Chandoo I think you've accidentally broke the conditional formatting for the alternating row colouring the formula is different to the version I sent you. As for the megaformula chrisham, it gave me a headache trying to get it all working, so I will let Chandoo talk you through it.
Hi,
In cells I6 and I7, I understand that subtotal together with offset function returns an array of ones after which, the sumproduct function gives the desired result.
But I’m not able to figure out the reason for using an array in I8 to return the most expensive gift.
Can’t the formula be just
“=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)”
Savithri, Cell I8 needs the array, if the formula was “=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)” it would find the highest price from the filtered range (i.e. highest actual in filtered range is $50) BUT then return the first person with that actual, not looking in just the filtered range (so first person on the list with a $50 actual.)
To see what I mean, change the formula, then change all the actuals to $50 then filter for baby, it lists the first name on the list.
But a good question 🙂
Thank you. I now realise that the array is used to get the ‘filtered range’ instead of the entire range, as table array for look up value.
[...] Download This Template [...]
this looks like an awesome excel sheet!! is there anyway i can get it emailed to me unprotected? for some reason, i am unable to download it 🙁 help!!
Hi I also can not download to a mac as the sheet is protected any help would be great
[...] to send her a pricey present. Rather, send a card with a picture of your child. Here’s a cool Excel sheet that will help you estimate your budget per person and let you track [...]
[...] husband and I pour/poor over the Christmas spreadsheet (yes, I do know how dorky that sounds, but we’re not the only ones!), figuring out who should give what to whom. We live at a distance from most of our family, so it [...]