Most Excel users have no idea the double dash feature even exists in Excel, but it can fix broken formulas, convert text to numbers, and even unlock calculations you normally can't do in Excel. So, let's break down what the double dash can really do with six practical examples from easy to hard. First up, in this basic example, suppose we have these numbers over here to the side, and we want to find out how many of them are greater than 10.
So you might think of just doing an equal sign, selecting all of these, and whenever they're greater than 10, we'll just hit enter there. And by the way, you can download this Excel file for free in the video description to follow along. Right now, these are in true or false.
So I can't easily go ahead and create a sum of these values because they're just in text right now. Instead, we can use the double dash. So equals this double dash, which is the same as the minus sign, and we'll select all of these values in here.
Hit enter there and you'll notice it converts the falses into zeros and the trus into ones. With that in mind, we can easily convert it into a formula. Now, so in here, I can just type some product add two dashes and in parenthesis here, we're going to say that this whole area has to be greater than 10.
We can close the parenthesis and close it again. This time for the sum product part. And now we have three, which makes sense as we've got three here that have a one.
Technically, you could have used the sum function as well. So, I could have just gone ahead and chosen sum instead of sum product, but keep in mind that only works with some of the newer Excel versions to get the same answer. And yes, I know what you're thinking.
Why not just use the count if? And that's a very good point. But now that you understand the syntax of the double dash, let's go over a more realistic example.
Here you can see we've got some prices for certain products. And if we double click in here, you'll notice that they've manually added the dollar sign. So it's not just a case of changing the format.
Instead, to clean this up and remove that dollar sign, we can use the text after function. And this is the whole area to the side, comma, and the delimiter. So what do we want to get rid of?
In this case, it's going to be the dollar sign in here. Close the parenthesis. And hit enter.
Now that's looking nice and clean. So you might think, why not just do the sum of all of these values, right? So, I'm just going to select sum.
That seems simple enough. But you'll notice we actually get a zero in here. Same thing if I go for something like the max.
I can just choose the max function instead. And you'll notice we have the same problem. That's actually because these values are not being detected as numbers.
Instead, they're being seen as text. So, we'll type equals sum and inside double dash and choose all of these value ranges. Close parenthesis and hit enter.
Now it's actually giving us the answer. Same thing with the max. So max double dash and simply select the relevant range which is this one up here.
Close up parenthesis and hit enter. So what exactly is going on here? And actually this double dash function works as a converter.
So it's converting it from a text value into a numerical value. The first negative turns it turns this number the 19,000 into a negative number. And then that second one turns it back into a positive, which in Excel just means that it's now converted to a number.
Awesome. The examples we've seen so far are actually still relatively basic. So, let's go over some more advanced ones over here.
As you can see, we have some dates on the side, and we want to find out how many of these dates are weekends. For this, we'll use the equals weekday function. Hit the tab key here.
This is our entire range. And the return type we wanted to start counting on Monday. So Monday means number one.
Close that parenthesis and hit enter. So basically here the days that are a six or a seven are those that are on weekends, right? So what we're going to do here is make that greater than five.
Hit enter there. But now we have the same problem as before with the true or the false. Instead we want this to be in numerical values.
So that's where the double dash comes handy. We can add it at the very start. open the parenthesis and close them at the end there.
So, we're now getting that converted into zeros and ones, which means that we can add a sum product in front to make that whole calculation. So, we get the answer in just one cell like this. So, how many weekends do we have?
We've got six days that are in weekends. If you want to understand this formula a bit better, here's a cool trick. You can actually go over to the formulas area and from here, click on evaluate formulas.
Here basically we can get a bit more of a breakdown of each of the answers in the process. So first we actually got the days simply with the weekday function. After that we went ahead and found whether it was true or false to eventually reach an an answer which in this case with the sum product is six.
Awesome. And before we get into more practical use cases, if you're liking this content and you want to learn more, you can also check out our range of courses which include learning Excel, PowerBI, Finance and valuation, as well as a range of bundles and programs. And what makes our courses different is that they're all applied to the real world.
So, aside from learning the theory, our lessons also offer case studies that simulate the type of work you might be assigned in your day-to-day, ranging from creating a financial model from scratch on Excel to creating a P&L dashboard on PowerBI all the way to making a professional pitch presentation on PowerPoint. And if you get stuck along the way, you can easily ask us, the course instructors, questions on the side. So, if you're interested in checking this out, head over to the link in the description below.
All right, back to the video. Next up, we've got a bit of a data cleaning use case. Over here, you can see we have some phone numbers, and we want to find out how many have nine digits.
So, for this, we can start by using the len function, which basically tells us how many digits there are or how many characters. So, you can see some are 10, some are nine, etc. But we want to find out how many of those are exactly nine.
So I can just put the double dash here and in parenthesis leave the len. But at the very end I'm going to say whenever it's equals to a nine close parenthesis and hit enter. So now we have the ones for all of the ones that are actually nine digits.
That means that on the top we can actually add the sum product and wrap it all up with it. Close up parenthesis and hit enter. So we've got a total of four 9-digit numbers.
And the great part here is that it's flexible once completed. So I can change this to not equals to A9 like this. Or I can easily do greater than or smaller than two.
Awesome. Now getting into the examples with multiple conditions like this one over here. Let's suppose we have some employees that have to pass an exam.
They have a theory part and a practical part. So we want to find out how many passed both the theory and the practice. In other words, we want to find out which of these have true on both.
So for this, we can type equals. The first part is this whole area right here whenever that equals to the word true. That's simple enough.
But of course, it's only one side of the criteria. Now, we want to find out for this whole other side. So this part right here should also equals to true.
If we just hit enter right now, we're going to get an error. So we need to do some changes in here. First, I'm going to wrap this around parenthesis like this.
So for this first part with a true and then for the second part as well just wrap that around in parenthesis. Once we have that set up we can actually do a sum product at the very start here and just close the parenthesis at the end. Make sure I have one at the start as well.
Hit enter here and we're actually getting zero as the answer. When we think about it that makes sense because the sum product doesn't actually work with text values. So that's where the double dash comes handy here.
Going to put it right here in between the two parenthesis as well as over here just after that comma. Hit enter there. And we now have three people that passed both the theory and the practice.
To check if that's true, we've got John here at the very start. We've got David. And finally, we've got John again at the end.
Finally, looking at the most advanced example. And here we'll make a good use of the some product tool. So you can see we have some employees with their division, whether they work on weekends and if they do their overtime hours and the overtime pay rate.
So we want to find out what's the overtime cost for sales employees working weekends. So it should say sales and it should be true that it's weekend. If those are the case, we then want to find out how many hours they work and multiply that by their rate.
The syntax here at the start should be somewhat similar to the previous, but this area right here, we want it to be equals to sales. Now, we should get a true or false whether that's the case or not. So, we have three TRs.
That makes sense as we have three matches. That's one part though. Now, let's actually wrap that in parenthesis.
Add a comma at the end. And the second criteria was that it has to be weekend. So, this part should say equals true for the weekend, right?
We'll close up parenthesis again at the start and at the end. So you might think why not just do the sum product like we did before over here. We'll add the parenthesis and those two dashes.
Same thing over on this side. Two dashes and then close it all together at the very end. Hit enter there.
That seems to be okay. But we're not actually looking for the number of matches this time. At the moment it's just found sales and true and sales and true over here.
So it's choosing those two and saying we've got two matches. We actually want a proper calculation though. So at the very end we're using the sum product.
So that means that we can select the number of hours. We can also select the overtime hourly rate right here and hit enter. With that you can see that it's going to cost us $160 for the sales employees that are working overtime on the weekend.
Great. So you can start to see just how versatile the double dash is in Excel. And another trick that you should learn is using the dot operator, which you can learn with this video over here or by taking our Excel course over here.
