Why is my SUMIF not working
In this video, learn why your SUMIF function may not be working correctly in Google Sheets and discover common pitfalls, such as spaces in criteria and mismatched ranges. The video also introduces the SUMIFS function as a more reliable alternative.
I'll try to help you with why your SUMIF is not working, and here's a few things to go through. So we have a SUMIF here. We have a range that we wanna look at, which is the month. And then we want to look at a criteria , which is going to be this C1 column. I could type in, in quotes, January, and then the sum range is gonna be this revenue here. Okay, so that actually does resolve, but there are some issues you may be having. So if I go into my reference and I add a space, with data validation , I see that that's different, but you might not see that's different. If I add a space here in January, I'll get zero. This space could also be in a reference , so I can select here and it would still
be zero unless I go and change this January to January without a space. But also, this space could be inside of your criteria here, where you're looking, and you might not know that you're missing out on some numbers here. So one quick fix there is to select Data , Data Cleanup , and Trim White Space. So right now you had none, but if we added a space- Add a space there, and then data , data cleanup , trim white. There it is. Two of them have been cleaned up. But even to prevent or rather see this issue before it happens, make
sure you have some data validation . This is a dropdown . That is a data validation . That's a type of data validation . Make sure you have that. Okay, another issue is that you've mixed up your range and your sum range . So get those correct. And what I actually like to do instead of using SUMIF is I will use SUMIFS because the sum range is the first one. So I'm like, "Oh, I wanna sum this up," comma, what's my criteria range? It's gonna be this C. What's my criteria? It's gonna be whatever's in C1. This makes sense to me and may make sense to you, and I won't get those wrong every single time. For some reason, SUMIF is reversed.
You have to look at what is the range you're looking at, then pick the criteria that you wanna s- sift by or filter by, and then the sum range So I like to think of it as SUMIF will give you the if first, then the sum. But SUMIFS will actually look at the sum, and then you can select the ifs. So that's a, a fun little heuristic to think about One common mistake too is that your number just isn't right. Like, it just looks weird, and it might be because you've combined different numbers here. We have a percentage and numbers here. So take a look at exactly the number. Also, one weird thing is sometimes it looks like a number, but
it is formatted as, as text. So what you need to do is, uh, select the cells , go up to Format , Number , and actually select a number here, um, as the format . Uh, that is, like, one of those weirdest things I've ever seen in Google Sheets, where , like, if everything looks fine, you're, you're getting the wrong number. You h- get no error, but, like, it's the wrong number. Check that. Uh, check that you're not viewing the number in a different way than the actual number underneath. So percentages will do that to you. Sometimes it'll say 5%, but in actuality it's . 05 in the data. So make sure your formatting is correct And also make sure your ranges are the same size. So if I do like G4, uh, and C here, with C:C, you see that's my error.
And it'll say array ar- arguments to SUMIFS are of different size. So if you see that, tho- that phrase, they're different sizes, just check that you're actually have the same exact range size, like the n- same number of columns, same number of rows in both of these. That will throw you an error in SUMIF and SUMIFS So even if I do G10 and C5, might be wrong. Just take a look at, um your actual range here. So on G to G and C to C So you see here we have two very different ranges as well And we're getting zero So we just need to make sure these ranges are the same You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google
sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.