Count and Sum values associated with two OR criteria - Sam or Sue. Bill and Mike provide many different formulas in this dueling Excel podcast.
Transcript of the video:
Hey, welcome back it's time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike from Excel Is Fun.
This is our episode 149.
Some Sam or Sue.
Hey, welcome back to the dueling Excel podcast.
Today's question send in from YouTube, how to count or some the sales for Sam or Sue, can you tell me the formula for that one.
You know what I'm not gonna do a formula there's an easy, easy way to do this, I insert a Pivot table, wanna put it on an existing worksheet right there click OK and what we wanna do is we want to have the sales reps going down the left hand side and then sales and now, we have the sum of sales for each person.
If we wanna see the count as well, we have to take a text field like sales rep and move out there and now, we get the count.
So, we know how many orders they had and the SUM.
Now, when Mike read this question he thought that they wanted the total for Sam plus Sue.
So, in other words and/or criteria and if that's the way it really is, and what we're gonna do is take sales rep and move it up to report filter and then select multiple items and choose both Sam and Sue, click OK and there's our some there's our count easy no formula needed.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Oh! Yeah, the Pivot table that is fast and easy.
Oh, but wait a second the person asks for a formula.
Hey, you know what I'm not going to do a formula yet either because there's a bunch of cool ways to do this and I, I do interpret this as or we want to add Sam or Sue all into one number.
So, check this out I'm gonna use the table feature, click in a single cell and the data set, control+T to convert it to a table, Enter.
Now, I'm gonna come below the sales column because we wanna add with two or criteria and then count with two or criteria.
So, I'm gonna click in the very last cell and use the keyboard for the autosum function Alt+equal, but because it's below a table it put in the subtotal.
Now, I'm going to come over here and there's a drop down.
So, I'm going to select count, not count numbers and now, I'm going filter uncheck, looks like the filter MrExcel used on the Pivot table, Sam, Sue, click OK and boom!
There we have it.
I'm going to throw it back to MrExcel, see if he has a formula.
Bill: Filter. Oh! hey, what you really have here this is a criteria range.
So, let's do this equal DSUM here's the database comma which field we want, field 3 comma and then the criteria range is right here, except for one very bad thing the criteria range has to have a valid heading sales rep BAM!
It works and I wasn't very careful about putting dollar signs in that's ok, let's do control+apostrophe which brings the exact same formula down and we'll just type DCOUNT instead of DSUM keeps all of the range's the same it doesn't lower them down by one and there's our count, but Mike I still think, I'm not convinced he's looking for the total of Sam or Sue, he used the word or here meaning count and sum I don't know, but anyway I will go with this.
Let's see what you have.
Mike: Oh! Yes, DSUM and DCOUNT in the D functions, those are beautiful functions.
Now, hey MrExcel, I gotta apologized that or I actually type that in the person who asked this question or typed this out head and there, but because being the teacher I am this is technically or criteria.
So, I changed it to or and created all this confusion.
So, the person really did wanna add Sam or Sue together and usually in language we use the word and anyway.
So, all right you know, I'm still not going to do a formula I know there's a bunch more formula as we could do.
I'm simply gonna go up to design because this is a table with a total row and add a slicer, when I add this slicer here.
I'm simply going to click on Sam and Sue.
Oh, wait a second how do you click oh, you have to hold the control key, if you want two items from your slicer, and then boom.
There we have a slicer and our subtotal functions for accounting sum.
All right, throw it back to MrExcel.
Bill: Slicers. Hey, I don't need Excel 2013 and your slicers.
My pivot table could do slicers here we'll take this sales rep out, Insert, Slicer, sales rep, click OK and I don't even need the control key, All okay remember that's funny, just click on Sam and drag down to Sue and baam!
They're both selected, but hey, let's face it we're dancing around this, right?
It's a formula and everyone out there watching those it's a formula.
Of course COUNTIF and SUMIF is the way to go Sam and Sue other employees are Dennis and Donna really handed that they started with D and Sam and Sue started with S.
So, check this out COUNTIFS a B6 to B20 the criteria is S star in quotes using a wild card same thing with the SUMIFS look through C6 to C20, B6 to B20 S star we get Sam and Sue.
Mike, over to you.
Mike: Man I love doing these duels man this week's been terrible.
I've been sick, my kids are sick and doing this duel back and forth is the most fun I've had and so, long and I get to learn this I had no idea that you could simply click and drag I thought you had to use the control trick look at that oh and then an asterisk a wild card.
Totally beautiful.
Now, I'm going to do a formula over here and you know for 2 or criteria you could simply use COUNTIF, two COUNTIF's right to add you simply use to SUMIF functions or SUMIF's for that matter.
Now, these are the formulas i have in my control+shift+enter book and I actually have a bunch of formulas for this, but Pmsocho at YouTube absolutely amazing YouTube channel.
He read my control+shift+enter book and then sent in this formula, equals hey, just use COUNTIF and highlight the range of names and comma in the criteria argument you just put both criteria because it's two of them COUNTIF we'll look through here and spit out two answers, this is a function argument array operation.
So, gonna highlight hit F9 of course COUNTIF will spit out both answers.
Well, if we need to add them, we could put it in SUM and use control+shift+enter, but why bother when you have SUMPRODUCT it can do array calculations without using any special keystroke to enter it and enter.
Now, of course for adding we simply do the range there's the all the criteria our function argument, array operation comma the sum range close, close and boom.
That is beautiful.
All right, throw back to MrExcel.
Bill: Hey, that was cool.
Putting both the criteria in the second argument of SUMIF's check this one out control+C, take the sales reps over here control+V, Data, Remove Duplicates, OK.
Now, that gets us just the unique values I'm gonna come over here and just put 1 and 1, 0 and 0.
Now, this trick I'm gonna use requires the list to be sorted.
So, we sort the list here's the count ready for this equal SUM of the old fashioned look up look up all of these names comma in this table the old fashioned look up always gets the right most column of the table it's always a comma true look up, close look up, close the sum, and control+shift+enter.
There's our answer. So, there's the count.
Now, let's take a look at this if i go to formulas, evaluate formula evaluate sure enough it's giving me an array of 1's and 0's.
So, I'm going to multiply that array of ones and zeros by those values there and I should be able to get the sum.
Let's try it out control+apostrophe will bring this down and then SUMPRODUCT of that look up that gives us the 1's and 0's comma this range here and are the parentheses balance yes we end up black , control+shift+enter, baam.
There we go.
Mike back to you.
Mike: That's the point right there MExcel.
That is amazing the most original solution in this whole video the old look up and a unique list 0's and 1's.
I absolutely love that man does that mean I have to come up with some more solutions.
Oh, no well, here's a silly one right, last time for accounting and I use SUMPRODUCT and COUNTIF.
Well, how about SUMPRODUCT's and COUNTIF's with an S or for adding SUMPRODUCT's and SUMIF's with an S.
Now, check this out some the last example I did for accounting and I did SUMPRODUCT in COUNTIF, well if you use SUM function, you have to use control+shift+enter, but watch this if I use that same formula, sum requires control+shift+enter because oh that's a function argument array operation, but check this out if i convert that from a range of cells, to an actual array constant I'm going to hit F9, there's those curly brackets, curly brackets house array constants, semicolon means row, comma means column if I leave it as an array constant, it won't require control+shift+enter.
Now, I cover this in my book not all formulas will take array constants without control+shift+enter, but it's amazing how many will, right?
So, here's that noted down there I just hit enter and boom that works, same with our SUM and SUMIF's if for some reason, you absolutely did not need this.
This criteria was never going to change then the golden rule of Excel is hey, if it's not going to change, then just leave it controls+Z, I highlighted the wrong one criteria F9.
If the golden rule is, if your formula inputs don't change just hard code them into your formula, then you don't have to use SUMPRODUCT just SUM and enter will do hey, a couple more straight from my control+shift+enter book there's that one is number match in side of SUMPRODUCT and even this one the old if with two arrays added together and then the value of true is one with control+shift+enter.
All right, throw it back to MrExcel.
Bill: Hey, well that was cool a record nine back and forth you know at the model often like the way that they score this the five thousand dollar prize there on that the finals is which formula is the shortest formula.
Now, there were so many formulas I've lost track of which one it was we'll have to go back and figure that out, but the one thing I loved in that at last for me when you showed that formula is cool, but what I really liked was let's say that we forgot to hit F4 something like that you actually clicked right here on the argument name and it selected that whole argument.
So, I could press F4 easily a lot easier than trying to go in there and choose the cells using the mouse just click there what a cool cool way to go.
All right, I wanna thank everyone for hanging in there and watching this whole thing.
We'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike from Excel Is Fun.
This is our episode 149.
Some Sam or Sue.
Hey, welcome back to the dueling Excel podcast.
Today's question send in from YouTube, how to count or some the sales for Sam or Sue, can you tell me the formula for that one.
You know what I'm not gonna do a formula there's an easy, easy way to do this, I insert a Pivot table, wanna put it on an existing worksheet right there click OK and what we wanna do is we want to have the sales reps going down the left hand side and then sales and now, we have the sum of sales for each person.
If we wanna see the count as well, we have to take a text field like sales rep and move out there and now, we get the count.
So, we know how many orders they had and the SUM.
Now, when Mike read this question he thought that they wanted the total for Sam plus Sue.
So, in other words and/or criteria and if that's the way it really is, and what we're gonna do is take sales rep and move it up to report filter and then select multiple items and choose both Sam and Sue, click OK and there's our some there's our count easy no formula needed.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Oh! Yeah, the Pivot table that is fast and easy.
Oh, but wait a second the person asks for a formula.
Hey, you know what I'm not going to do a formula yet either because there's a bunch of cool ways to do this and I, I do interpret this as or we want to add Sam or Sue all into one number.
So, check this out I'm gonna use the table feature, click in a single cell and the data set, control+T to convert it to a table, Enter.
Now, I'm gonna come below the sales column because we wanna add with two or criteria and then count with two or criteria.
So, I'm gonna click in the very last cell and use the keyboard for the autosum function Alt+equal, but because it's below a table it put in the subtotal.
Now, I'm going to come over here and there's a drop down.
So, I'm going to select count, not count numbers and now, I'm going filter uncheck, looks like the filter MrExcel used on the Pivot table, Sam, Sue, click OK and boom!
There we have it.
I'm going to throw it back to MrExcel, see if he has a formula.
Bill: Filter. Oh! hey, what you really have here this is a criteria range.
So, let's do this equal DSUM here's the database comma which field we want, field 3 comma and then the criteria range is right here, except for one very bad thing the criteria range has to have a valid heading sales rep BAM!
It works and I wasn't very careful about putting dollar signs in that's ok, let's do control+apostrophe which brings the exact same formula down and we'll just type DCOUNT instead of DSUM keeps all of the range's the same it doesn't lower them down by one and there's our count, but Mike I still think, I'm not convinced he's looking for the total of Sam or Sue, he used the word or here meaning count and sum I don't know, but anyway I will go with this.
Let's see what you have.
Mike: Oh! Yes, DSUM and DCOUNT in the D functions, those are beautiful functions.
Now, hey MrExcel, I gotta apologized that or I actually type that in the person who asked this question or typed this out head and there, but because being the teacher I am this is technically or criteria.
So, I changed it to or and created all this confusion.
So, the person really did wanna add Sam or Sue together and usually in language we use the word and anyway.
So, all right you know, I'm still not going to do a formula I know there's a bunch more formula as we could do.
I'm simply gonna go up to design because this is a table with a total row and add a slicer, when I add this slicer here.
I'm simply going to click on Sam and Sue.
Oh, wait a second how do you click oh, you have to hold the control key, if you want two items from your slicer, and then boom.
There we have a slicer and our subtotal functions for accounting sum.
All right, throw it back to MrExcel.
Bill: Slicers. Hey, I don't need Excel 2013 and your slicers.
My pivot table could do slicers here we'll take this sales rep out, Insert, Slicer, sales rep, click OK and I don't even need the control key, All okay remember that's funny, just click on Sam and drag down to Sue and baam!
They're both selected, but hey, let's face it we're dancing around this, right?
It's a formula and everyone out there watching those it's a formula.
Of course COUNTIF and SUMIF is the way to go Sam and Sue other employees are Dennis and Donna really handed that they started with D and Sam and Sue started with S.
So, check this out COUNTIFS a B6 to B20 the criteria is S star in quotes using a wild card same thing with the SUMIFS look through C6 to C20, B6 to B20 S star we get Sam and Sue.
Mike, over to you.
Mike: Man I love doing these duels man this week's been terrible.
I've been sick, my kids are sick and doing this duel back and forth is the most fun I've had and so, long and I get to learn this I had no idea that you could simply click and drag I thought you had to use the control trick look at that oh and then an asterisk a wild card.
Totally beautiful.
Now, I'm going to do a formula over here and you know for 2 or criteria you could simply use COUNTIF, two COUNTIF's right to add you simply use to SUMIF functions or SUMIF's for that matter.
Now, these are the formulas i have in my control+shift+enter book and I actually have a bunch of formulas for this, but Pmsocho at YouTube absolutely amazing YouTube channel.
He read my control+shift+enter book and then sent in this formula, equals hey, just use COUNTIF and highlight the range of names and comma in the criteria argument you just put both criteria because it's two of them COUNTIF we'll look through here and spit out two answers, this is a function argument array operation.
So, gonna highlight hit F9 of course COUNTIF will spit out both answers.
Well, if we need to add them, we could put it in SUM and use control+shift+enter, but why bother when you have SUMPRODUCT it can do array calculations without using any special keystroke to enter it and enter.
Now, of course for adding we simply do the range there's the all the criteria our function argument, array operation comma the sum range close, close and boom.
That is beautiful.
All right, throw back to MrExcel.
Bill: Hey, that was cool.
Putting both the criteria in the second argument of SUMIF's check this one out control+C, take the sales reps over here control+V, Data, Remove Duplicates, OK.
Now, that gets us just the unique values I'm gonna come over here and just put 1 and 1, 0 and 0.
Now, this trick I'm gonna use requires the list to be sorted.
So, we sort the list here's the count ready for this equal SUM of the old fashioned look up look up all of these names comma in this table the old fashioned look up always gets the right most column of the table it's always a comma true look up, close look up, close the sum, and control+shift+enter.
There's our answer. So, there's the count.
Now, let's take a look at this if i go to formulas, evaluate formula evaluate sure enough it's giving me an array of 1's and 0's.
So, I'm going to multiply that array of ones and zeros by those values there and I should be able to get the sum.
Let's try it out control+apostrophe will bring this down and then SUMPRODUCT of that look up that gives us the 1's and 0's comma this range here and are the parentheses balance yes we end up black , control+shift+enter, baam.
There we go.
Mike back to you.
Mike: That's the point right there MExcel.
That is amazing the most original solution in this whole video the old look up and a unique list 0's and 1's.
I absolutely love that man does that mean I have to come up with some more solutions.
Oh, no well, here's a silly one right, last time for accounting and I use SUMPRODUCT and COUNTIF.
Well, how about SUMPRODUCT's and COUNTIF's with an S or for adding SUMPRODUCT's and SUMIF's with an S.
Now, check this out some the last example I did for accounting and I did SUMPRODUCT in COUNTIF, well if you use SUM function, you have to use control+shift+enter, but watch this if I use that same formula, sum requires control+shift+enter because oh that's a function argument array operation, but check this out if i convert that from a range of cells, to an actual array constant I'm going to hit F9, there's those curly brackets, curly brackets house array constants, semicolon means row, comma means column if I leave it as an array constant, it won't require control+shift+enter.
Now, I cover this in my book not all formulas will take array constants without control+shift+enter, but it's amazing how many will, right?
So, here's that noted down there I just hit enter and boom that works, same with our SUM and SUMIF's if for some reason, you absolutely did not need this.
This criteria was never going to change then the golden rule of Excel is hey, if it's not going to change, then just leave it controls+Z, I highlighted the wrong one criteria F9.
If the golden rule is, if your formula inputs don't change just hard code them into your formula, then you don't have to use SUMPRODUCT just SUM and enter will do hey, a couple more straight from my control+shift+enter book there's that one is number match in side of SUMPRODUCT and even this one the old if with two arrays added together and then the value of true is one with control+shift+enter.
All right, throw it back to MrExcel.
Bill: Hey, well that was cool a record nine back and forth you know at the model often like the way that they score this the five thousand dollar prize there on that the finals is which formula is the shortest formula.
Now, there were so many formulas I've lost track of which one it was we'll have to go back and figure that out, but the one thing I loved in that at last for me when you showed that formula is cool, but what I really liked was let's say that we forgot to hit F4 something like that you actually clicked right here on the argument name and it selected that whole argument.
So, I could press F4 easily a lot easier than trying to go in there and choose the cells using the mouse just click there what a cool cool way to go.
All right, I wanna thank everyone for hanging in there and watching this whole thing.
We'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
