Part of my time this week was spent working on a project for a client and I was sooo frustrated. I just couldn’t get my SUMIFS formula to work.
Have you ever had that issue with an Excel function?
This formula was a simple (or at least I thought simple) SumIFs function that used relative references, absolute references and Excel Table reference over a very large range.
Well normally, I would use the Formulas Ribbon and then click on the Evaluate Formula button to trace my issue, however, because of the length of the range and that it was erroring out, I couldn’t find the exact issue. I must admit, that I hadn’t known about this GREAT tool until a few years ago and I wish I had known so much sooner. I will have to post a video showing you how to use it if you are not familiar.
SO, I replicated the formula using the Excel SumProduct function (Which I think is awesome), but I didn’t think the client would be able to maintain the function on their own. So I needed to fix the SumIFs issue.
I tried to break it down and just put in 1 criteria that validated the name. and it works.
Then I added another criteria that added a date component to the formula to check that the date was >= a value and it failed.
So I then simplified the formula criteria 2 to check for a specific date and it worked.
Therefore, I am doing something wrong. Lets add back in the >= condition.
Here was my formula:
=SUMIFS(Table1[Amount],Table1[Category],$A120,Table1[Date],”>=B$2″)
Do you see what is wrong? I didn’t, but I know it is in the 2nd criteria the “>=B$2″ section.
So I tried this:
=SUMIFS(Table1[Amount],Table1[Category],$A120,Table1[Date],”>=”B$2) [I just moved the right quotation mark ” before the B$2. That should work, right? But NO!
I then Check Microsoft Excel Help:
Here are their Examples:
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- =SUMIFS(A2:A9,B2:B9,”=A*”,C2:C9,”Tom”)
- =SUMIFS(A2:A9,B2:B9,”<>Bananas”,C2:C9,”Tom”)
| Formula | Description | |
| =SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”) | Adds the number of products that begin with A and were sold by Tom. It uses the wildcard character * in Criteria1, “=A*” to look for matching product names in Criterial_range1 B2:B9, and looks for the name “Tom” in Criterial_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 20. | |
| =SUMIFS(A2:A9, B2:B9, “<>Bananas”, C2:C9, “Tom”) | Adds the number of products that aren’t bananas and are sold by Tom. It excludes bananas by using <> in the Criteria1, “<>Bananas”, and looks for the name “Tom” in Criterial_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 30. |
Nothing like I am doing. They are only checking for exact values and not a cell value.
No help to my issue.
I then go to a web search. And after 8-10 web pages, my issue finally comes to light….
I am missing an ampersand ‘&’
Here is what the formula should look like:
=SUMIFS(Table1[Amount],Table1[Category],$A120,Table1[Date],”>=”&B$2)
Do you see the issue?
I didn’t have a the Cell Reference and the Conditional Operator joined. I was trying to combine them all in the same area by putting them in the quotation marks or simply after the quotation marks. It never occurred to me that I needed to join them with an ampersand operator, but makes sense now. Guess I need to use these formulas more 🙂
I needed to add a string PLUS the cell value. Since the Help file didn’t have this as an example, I thought I would post this for you and all the future me’s.
By the way, I can’t remember all of the techniques. I frequently use MY OWN site to review how I solved an issue previously. For instance, on the last Friday Challenge, I went to this site and searched for the terms that I remembered until I found what I was looking for: how-to-convert-an-existing-excel-data-set-to-a-pivot-table-format
I then reviewed the Excel Tip / Trick and implemented the technique. So really, I am posting this so that the NEXT time I am trying to use the SUMIFS function and it is not working, I will know that i need to add the ‘&’ before the cell value 🙂
Hopefully this will save you several minutes of frustration when it comes to the right syntax for an Excel function.
What function did you spend a lot of time trying to figure out and then it was a simply syntax issue? Let me know in the comments below.
Also, consider subscribing to the newsletter so that you will get the next post delivered directly to your inbox.
Steve=True
