Demystifying the SumIF Formula. Add up your values based on a condition, even if that condition is a date.
Academy ↓
Hey there stranger!
Hardest Formula: SumIF | Addition Based on Dates / Values
About this Tutorial
Sheet Resources
Video Transcript
0:00 All right. Is this the hardest formula to figure out? The some if formula? I've heard this question a few times and in many different ways.
0:10 Almost every single time that someone asks about this, and the answer is some, if the question is almost always different some people want to add up all of the rows that contain something.
0:24 They want to add up the categories. They have duplicate categories and they have a value they want to add up and they want the result.
0:33 We just had a member post and forum asking this question, and the question took, you know, two and a half minutes to que to question.
0:44 But I realized not just then, but also before, this is a very hard formula to remember. Even though it is only five letters, it is some the add up, the addition of right?
0:58 And if it has the if in there of like, we want to te test a column and when there is this thing, if there is this thing, we want it to add up a value on somewhere else.
1:11 So I'll show you how this is wor works, but I want to show you here. It's also one of the most Googled Google Sheet formulas.
1:18 Cuz if you type in Google sheets in Google and you see like there's app, there's online currency conversion, which is not specifically a named function.
1:28 I mean there are currency conversions cuz that's also like, that's what is it? Oh my god. <laugh> Google Finance. That's, so, that's hard to figure out that you want to convert currency, you have Google Finance, you need to do merge cells.
1:43 That's a menu option. You can't do that in a formula. I don't know what aala is. Date format formatting in Excel and Google Sheets has always been terrible and always frustrating.
1:53 But here, the first actually named Google Sheet formula is some, if the second one is, the next one down is, is filter, which is also one of those things like how do you remember the word filter?
2:05 Now if you've been using Google Sheets for 10, 15 years, then maybe some if is one of those formulas you use a lot, especially if you're in accounting.
2:14 But if you're not an accountant, if you're not in bookkeeping, like some if is a little bit foreign. So we'll go through that right now.
2:21 I'm gonna show you the syntax of how to use it. So in, in the way that I like to use it, I like to use it to create a little summary.
2:28 Here, I'll, I'll make the summary here, but I always will move it to another page. We'll use first unique to grab all the unique statuses here.
2:37 That's just lost one started. This is like some kind of crm. You have some value of potential jobs and you're reaching out to them and you're making sure they're flowing properly and that you have a proper amount of maybe almost complete projects or almost started projects.
2:57 And here we're gonna go with equal some if, and we're gonna take the range. We're gonna use the entire column of a.
3:05 Now the issue with some if is your result is the addition, the, the combination of all the values. But what we actually need to do the first range is where the if is that's what we need to do.
3:23 So even though we're thinking of the result, the value, the the B column here, we need to start in the A column cuz that's where our if is based.
3:32 So we select that a column by just doing a colon A and the criterion is, in this case, I already have used unique to grab it.
3:42 So unique helps us here. We have lost, we could also type this so we can say lost and we can have text right here.
3:49 I do this a lot. If I don't know what will end up happening, like I don't know if I'm gonna get it unique.
3:54 I don't know where I'm gonna, I'm just gonna type in a word and do it. But here we have our word already in this referenced E three.
4:03 Now the sum range, this is the final result. This is how we get all of our things added together and we're gonna use b2b.
4:11 Now I click through this, but you can also type this out. B, colon B. Now the issue is we are going to have to format this.
4:18 It doesn't necessarily know what the format is. So in our case it is money value currency. So we will change that.
4:25 And now, because I have used a reference in that second part for the criterion, I can copy paste this down and I get the sum.
4:36 Now if I sum everything here, it's 52 K. If I sum all this, it's 52 K. So we know we are correct, we know we haven't lost anything either because this is a unique function here.
4:47 So unique helps us know that all of our status is here. The ifs are accounted for. One of the problems I've run into with this formula before is that I don't account for everything.
4:58 So the total in this sum of all some ifs are not the total of everything. That's one error you might run in or not error, but roadblock you might run into.
5:08 You also might have a more considerably more granular condition. That's this center part. There are criterion as they call it.
5:18 I call it condition, but we want this condition to be something we can tell this gets a little comp can get a little complicated.
5:27 So let's say we have a date here. This is gonna get really interesting. Let's say we have all these dates are not just one one, but maybe they're some February this as well.
5:42 Well, and maybe this is date, last date contacted and we're like, we just wanna see what is the what, what is, is the total of value here that our last date contacted was before February.
6:03 This might get a little complicated, right? So we go here, contacted before February. Okay, we're gonna sum if the first sum, if is going to be the range is going to be actually C here and the condition is going to be less than 2 1 22.
6:35 Oops, I totally made a mistake there. Okay, here, this is gonna be actually much easier. So what ends up happening with dates is that this date is actually a number 44, 9 27.
6:46 This date, February 2nd is 44. 959. That's the amount of days since January 1st, 1900. So we are gonna take 44, 58 is February 1st, so 4 4 9 58.
7:03 And what we need to do is make sure that we're under, in quotes, less than that, let's say in quotes, less than 4 4 98.
7:16 Hmm. Now let's see if this is correct, <laugh>. So we need to, we put in quotes the criterion, which is less than the number, the day number that we want, which is February 1st.
7:30 We figured out February 1st is this number four 44,958. And now is this the 44,934? Let's double check that. So it's this value we're just gonna select with the command key, all of the ones that are less than February 2nd.
7:46 Now we have our sum down here. Move my face or sum down here. 44,934. It is exactly correct. So we have now figured out our sum if based on a date, which is very difficult, right?
7:59 You would not know this intuitively from looking at this sum if syntax, that it's range criterion, sum range and this sum range is even optional.
8:09 But this criterion is very difficult to understand that you need to put the criterion inside of quotes. So I hope this helped you understand the sum if formula and now you can do dashboards more efficiently.
8:22 You can also do some ifs with dates more efficiently. Thought that would be really fun and really interesting. Let me know down below if you need any more information about the sum if formula.
0:10 Almost every single time that someone asks about this, and the answer is some, if the question is almost always different some people want to add up all of the rows that contain something.
0:24 They want to add up the categories. They have duplicate categories and they have a value they want to add up and they want the result.
0:33 We just had a member post and forum asking this question, and the question took, you know, two and a half minutes to que to question.
0:44 But I realized not just then, but also before, this is a very hard formula to remember. Even though it is only five letters, it is some the add up, the addition of right?
0:58 And if it has the if in there of like, we want to te test a column and when there is this thing, if there is this thing, we want it to add up a value on somewhere else.
1:11 So I'll show you how this is wor works, but I want to show you here. It's also one of the most Googled Google Sheet formulas.
1:18 Cuz if you type in Google sheets in Google and you see like there's app, there's online currency conversion, which is not specifically a named function.
1:28 I mean there are currency conversions cuz that's also like, that's what is it? Oh my god. <laugh> Google Finance. That's, so, that's hard to figure out that you want to convert currency, you have Google Finance, you need to do merge cells.
1:43 That's a menu option. You can't do that in a formula. I don't know what aala is. Date format formatting in Excel and Google Sheets has always been terrible and always frustrating.
1:53 But here, the first actually named Google Sheet formula is some, if the second one is, the next one down is, is filter, which is also one of those things like how do you remember the word filter?
2:05 Now if you've been using Google Sheets for 10, 15 years, then maybe some if is one of those formulas you use a lot, especially if you're in accounting.
2:14 But if you're not an accountant, if you're not in bookkeeping, like some if is a little bit foreign. So we'll go through that right now.
2:21 I'm gonna show you the syntax of how to use it. So in, in the way that I like to use it, I like to use it to create a little summary.
2:28 Here, I'll, I'll make the summary here, but I always will move it to another page. We'll use first unique to grab all the unique statuses here.
2:37 That's just lost one started. This is like some kind of crm. You have some value of potential jobs and you're reaching out to them and you're making sure they're flowing properly and that you have a proper amount of maybe almost complete projects or almost started projects.
2:57 And here we're gonna go with equal some if, and we're gonna take the range. We're gonna use the entire column of a.
3:05 Now the issue with some if is your result is the addition, the, the combination of all the values. But what we actually need to do the first range is where the if is that's what we need to do.
3:23 So even though we're thinking of the result, the value, the the B column here, we need to start in the A column cuz that's where our if is based.
3:32 So we select that a column by just doing a colon A and the criterion is, in this case, I already have used unique to grab it.
3:42 So unique helps us here. We have lost, we could also type this so we can say lost and we can have text right here.
3:49 I do this a lot. If I don't know what will end up happening, like I don't know if I'm gonna get it unique.
3:54 I don't know where I'm gonna, I'm just gonna type in a word and do it. But here we have our word already in this referenced E three.
4:03 Now the sum range, this is the final result. This is how we get all of our things added together and we're gonna use b2b.
4:11 Now I click through this, but you can also type this out. B, colon B. Now the issue is we are going to have to format this.
4:18 It doesn't necessarily know what the format is. So in our case it is money value currency. So we will change that.
4:25 And now, because I have used a reference in that second part for the criterion, I can copy paste this down and I get the sum.
4:36 Now if I sum everything here, it's 52 K. If I sum all this, it's 52 K. So we know we are correct, we know we haven't lost anything either because this is a unique function here.
4:47 So unique helps us know that all of our status is here. The ifs are accounted for. One of the problems I've run into with this formula before is that I don't account for everything.
4:58 So the total in this sum of all some ifs are not the total of everything. That's one error you might run in or not error, but roadblock you might run into.
5:08 You also might have a more considerably more granular condition. That's this center part. There are criterion as they call it.
5:18 I call it condition, but we want this condition to be something we can tell this gets a little comp can get a little complicated.
5:27 So let's say we have a date here. This is gonna get really interesting. Let's say we have all these dates are not just one one, but maybe they're some February this as well.
5:42 Well, and maybe this is date, last date contacted and we're like, we just wanna see what is the what, what is, is the total of value here that our last date contacted was before February.
6:03 This might get a little complicated, right? So we go here, contacted before February. Okay, we're gonna sum if the first sum, if is going to be the range is going to be actually C here and the condition is going to be less than 2 1 22.
6:35 Oops, I totally made a mistake there. Okay, here, this is gonna be actually much easier. So what ends up happening with dates is that this date is actually a number 44, 9 27.
6:46 This date, February 2nd is 44. 959. That's the amount of days since January 1st, 1900. So we are gonna take 44, 58 is February 1st, so 4 4 9 58.
7:03 And what we need to do is make sure that we're under, in quotes, less than that, let's say in quotes, less than 4 4 98.
7:16 Hmm. Now let's see if this is correct, <laugh>. So we need to, we put in quotes the criterion, which is less than the number, the day number that we want, which is February 1st.
7:30 We figured out February 1st is this number four 44,958. And now is this the 44,934? Let's double check that. So it's this value we're just gonna select with the command key, all of the ones that are less than February 2nd.
7:46 Now we have our sum down here. Move my face or sum down here. 44,934. It is exactly correct. So we have now figured out our sum if based on a date, which is very difficult, right?
7:59 You would not know this intuitively from looking at this sum if syntax, that it's range criterion, sum range and this sum range is even optional.
8:09 But this criterion is very difficult to understand that you need to put the criterion inside of quotes. So I hope this helped you understand the sum if formula and now you can do dashboards more efficiently.
8:22 You can also do some ifs with dates more efficiently. Thought that would be really fun and really interesting. Let me know down below if you need any more information about the sum if formula.