I think I stumbled upon an Excel “bug” (gasp!!) when I was combining SUMIF formulas with sorting alphabetically. @___@ This is a new one, grasshoppers and honeybees! Sit down, buckle in, and enjoy the ride – we’re gonna get technical with some formulas and explore what to do when you get real-deal confused. I’m glad I stuck with it even when I got frustrated, because I ended up learning about this weird bug AND getting to the bottom of my problem.
Setting the stage
Here’s the situation I was tackling: My colleague needed a report of grants, but only with organizations that met some criteria. So, first I exported a list of all grants. Then, I filtered based on the criteria I was given. Then, I copied that filtered list of grants and de-duped by Organization Name. Then, I went back to the original Grants Report and filtered that report by the entire list of Organization Names (using a VLOOKUP). If your eyes are fogging over right now stick with me! And if you’re enthralled – and you know a better way to do this order of operations – drop me a line! This is the most efficient steps I’ve found so far, but it’s not very elegant and not easily repeatable if the criteria do change.
Anyway. End result is: a list of grants awarded to particular organizations. COOL. Now, we need summaries by Organization so someone doesn’t have to scroll through the entire list to glean insights. I determined that the best way to do this was to make a new tab with a list of each organization in the first column, and then use SUMIFS to sum-marize (har har) the grant data by org. Great!
For example, in the screenshot above, you can see that there’s 3 companies (A, B, C) and there is a formula that only adds the Sales Revenue IF (get it? SUM + IF) the Company is Company A.
The SUMIF function is super powerful and there are lots of great applications for changemakers. (BTW, if you have more than one criterion, you use the SUMIFS function instead of SUMIF). For example,
- SUMIFS zipcode = (your zip code) and Volunteer Status = Active
- SUMIFS Donation Status = “Pledged” and Donation Amount = “greater than $500”
- SUMIFS Donation Date = “this fiscal year” and Donation Note contains “in memory of”
- Want to get advanced with SUMIFS examples? Check out this great tutorial.
You might be thinking, why not just filter your data or summarize your data in Salesforce or use a pivot table? Well my friend, that is probably a blog post for another day. But the brief answer is, I like SUMIFS on summary tabs if we are showing totals based on different criteria. For example, if the first column is a list of organizations, it’s cool to use SUMIFS to show “grants this year,” “grants last year,” “grants in this strategy,” “grants in that strategy.” Like I said, there might be a better way to do this, but my way has been working for now! In Salesforce reports or Pivot Tables, you could only show one summary scenario at a time.
I was happy with what I had accomplished so far, but I decided, “ya know what, I think it would be better to sort this list of Organizations alphabetically!” So I did my usual trick… Format as Table, Sort A->Z and then I noticed that the SUMIF formulas were completely messed up! AGH!
Of course I did a CTRL+Z (undo!) to make sure that the calculations had indeed changed (that they weren’t wrong the whole time!). And after a couple test rounds, I realized that for some weird reason, sorting was breaking my formulas. This left me in a bit of a pickle. All other formulas re-calculate when you re-arrange data. Why would this be any different?
I went down an internet rabbit hole, and tried all kinds of different methods. Some forums suggested that I replace my relative cell names with absolute cell names (ie instead of cell A2, write $A$2). I’ll spare you the gory details, but suffice it to say, if that had worked, I might not be writing this blog post.
Deep in some random message boards, I saw another possible solution. Delete the name of the Excel Tab I was in (my spreadsheet had multiple tabs!) and then try again. Miraculously, it worked! Here’s what I learned: If you are in Sheet 1, you shouldn’t type “Sheet1!A1” in your formula. You should only type “A1” since adding Sheet 1 is redundant. In other words,
The problem is, as you say, a result of including the Sheet1! in the formula.
Sort sees the sheet reference and decides, wrongly, that it isn’t a reference to a cell on the same row within the data that is being sorted, so it treats it like a reference to a cell outside the area being sorted and moves the formula unchanged along with the item to its left.
I agree that this is annoying and it annoys me too that the sheet name is automatically included when you have visited another sheet earlier in the formula and then return to the sheet on which you are entering the formula.
The only solution I know is to manually remove the Sheet1! from the first formula before copying it down to the rest of the table.
https://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/excel-sort-messing-up-formulas/3c16e851-e6dd-4f95-9fe4-91726340e579
UGH!! This is exactly what happened to me! I never type “Sheet1…” I just click to the cells that I need to reference in my formulas. It was really hard to find a decent explanation of this phenomenon (bug??) on the interwebs… perhaps because sometimes this issue pops up with other formulas but I had limited myself to searching for errors with the SUMIFS function, since that’s where *I* had the problem. I tried to find a “known error” log with Microsoft, but to no avail. I’m probably not even explaining it well here, but it was such a weird issue that I decided I *had* to write about it.
As frustrating as this process was, I do feel a sense of accomplishment for figuring out the issue. I never would have gotten to the bottom of it without help online, trial and error, guess and check, patience, and stubbornness. I was ready to give up and either (a) re-do my spreadsheet, sorting the Org List alphabetically BEFORE putting in formulas or (b) leaving it out of order and hoping for the best. Both of those are bad options because what if my co-worker had sorted the data and didn’t realize right away that the values were inaccurate? There could have been a real waterfall effect!
So changemakers, there are a couple of take-aways I want to share with you from this story!
- SUMIFS are a powerful function! If you ever want to give them a whirl, but you’re not sure how, feel free to get in touch with me and we can do a quick lesson!
- Sometimes spreadsheets have glitches that are NOT YOUR FAULT! I try to remind myself that technology is made by real people, and sometimes there are quirks that are out of our control.
- If you get stuck, you are still a super-awesome, pie-a-la-mode-deserving, loveable, fierce, smart spreadsheet rockstar! Even trying to make complex formulas (because you are motivated by a larger goal) is an amazing thing to do. You deserve all of the affirmation!!!! I am cheering you on!
- If you get stuck (part 2), you can experiment, tinker, walk away, come back, get stubborn, ask for help, learn something new, start over and try again, etc. You have CHOICES! And as my friend Emily likes to say, the second time around is usually faster and almost always better!
I’m not going to face this exact challenge again (believe me, I learned my lesson) but I know I’ll face other spreadsheet pitfalls. In my line of work, they are pretty much guaranteed (for better or for worse!). But the good news is, I never have to tackle them alone.