Stevenson asks: Why won't this SUMIFS work inside of a LET function in Excel?
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of "There's a Problem With This Formula".
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, DSUM, DCOUNT, DAVERAGE and so on.
The workaround is to switch to a FILTER function inside of the LET or simply a FILTER function inside of a SUM.
Table of Contents
(0:00) SUMIFS inside of LET
(1:13) Any calc as first LET argument
(1:47) Move calculation to grid
(2:12) Using FILTER instead
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of "There's a Problem With This Formula".
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, DSUM, DCOUNT, DAVERAGE and so on.
The workaround is to switch to a FILTER function inside of the LET or simply a FILTER function inside of a SUM.
Table of Contents
(0:00) SUMIFS inside of LET
(1:13) Any calc as first LET argument
(1:47) Move calculation to grid
(2:12) Using FILTER instead
Transcript of the video:
Today's question for the Financial Modelling Summit.
Why won't this SUMIFS work inside of a LET function?
I just finished up three days of Financial Modelling Summit 2022. Great content there.
I love the Excel from Liam and me and Jordan Goldmeier, and a lot of other great topics.
The recordings are still out there. Check it out.
I think it's $50 bucks or something like that, and you can get the recordings for a year.
So, one of the questions that came in while I was doing my session on what's new, the new functions in Excel. It seemed like a really good idea.
We have a simple little database here, A, B, A, B, A, B, and some numbers.
And Stevenson wanted to basically assign those numbers to the variable A, and then do some sort of a transformation.
In his case, it was just A plus one, but let's make it more difficult. Divide the A by 10, and then square the number.
Okay.
And then the last part, the calculation is the SUMIFS passed those transformed numbers just for the items that are A.
And when we press enter, we get value errors all the way down.
I'm like I mean, it really should work, right? But it turns out that this is against the rules.
The first argument in SUMIFS cannot take a calculation.
And had we tried to enter that just as a SUMIFS, and by the way it applies to COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIFS, COUNTIF, all of the D functions, DSUM, DAVERAGE, DCOUNT, we would've just tried to enter this, the parser would know that this is never going to fly.
They don't tell us that it's not going to fly. It's kind of an annoying message.
There's a problem with this formula. They don't tell you what the problem is.
But the problem is that you can't use a calculation as the first part of SUMIFS.
Okay, so what's the work around then?
If we were just using SUMIFS, we would have to put that calculation into the grid with surfaced into the grid.
So, B20 divided by 10, raised to the second power.
And then we could just use a SUMIFS based on Column C.
But we want to do this inside of a LET.
So unfortunately, I think at least for me, the people in the YouTube comments will tell me that there's other ways to do this.
I'm going to have to resort to a filter.
So, just trying to use the LET function, LET A equal the filter of these values in B, where these values in A are equal to the letter A.
And then once I have that filtered data set, so now I'm just going to get the 100, the 200, the 400, I will define that as A divided by 10, raised to the second power, and then sum of B.
I was just trying to emulate the original formula here.
Turns out we don't even have to use the LET.
If you're really careful with the parentheses, the first time I did this, the parentheses were wrong and I ended up with the wrong number.
So, filter B3 to where A3 is equal to A, divide by 10, raise the second power, and then sum that entire thing, we get the exact same answer.
Shout out to Brad [ inaudible 00:03:05 ], Charles Williams, Andrew Becker, who already knew this.
As soon as I asked the question, they're like, oh yeah, that's never going to work. Never has, never will.
Super annoying that the message when you're trying to do it inside of SUMIFS isn't so clear.
All right. Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Why won't this SUMIFS work inside of a LET function?
I just finished up three days of Financial Modelling Summit 2022. Great content there.
I love the Excel from Liam and me and Jordan Goldmeier, and a lot of other great topics.
The recordings are still out there. Check it out.
I think it's $50 bucks or something like that, and you can get the recordings for a year.
So, one of the questions that came in while I was doing my session on what's new, the new functions in Excel. It seemed like a really good idea.
We have a simple little database here, A, B, A, B, A, B, and some numbers.
And Stevenson wanted to basically assign those numbers to the variable A, and then do some sort of a transformation.
In his case, it was just A plus one, but let's make it more difficult. Divide the A by 10, and then square the number.
Okay.
And then the last part, the calculation is the SUMIFS passed those transformed numbers just for the items that are A.
And when we press enter, we get value errors all the way down.
I'm like I mean, it really should work, right? But it turns out that this is against the rules.
The first argument in SUMIFS cannot take a calculation.
And had we tried to enter that just as a SUMIFS, and by the way it applies to COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIFS, COUNTIF, all of the D functions, DSUM, DAVERAGE, DCOUNT, we would've just tried to enter this, the parser would know that this is never going to fly.
They don't tell us that it's not going to fly. It's kind of an annoying message.
There's a problem with this formula. They don't tell you what the problem is.
But the problem is that you can't use a calculation as the first part of SUMIFS.
Okay, so what's the work around then?
If we were just using SUMIFS, we would have to put that calculation into the grid with surfaced into the grid.
So, B20 divided by 10, raised to the second power.
And then we could just use a SUMIFS based on Column C.
But we want to do this inside of a LET.
So unfortunately, I think at least for me, the people in the YouTube comments will tell me that there's other ways to do this.
I'm going to have to resort to a filter.
So, just trying to use the LET function, LET A equal the filter of these values in B, where these values in A are equal to the letter A.
And then once I have that filtered data set, so now I'm just going to get the 100, the 200, the 400, I will define that as A divided by 10, raised to the second power, and then sum of B.
I was just trying to emulate the original formula here.
Turns out we don't even have to use the LET.
If you're really careful with the parentheses, the first time I did this, the parentheses were wrong and I ended up with the wrong number.
So, filter B3 to where A3 is equal to A, divide by 10, raise the second power, and then sum that entire thing, we get the exact same answer.
Shout out to Brad [ inaudible 00:03:05 ], Charles Williams, Andrew Becker, who already knew this.
As soon as I asked the question, they're like, oh yeah, that's never going to work. Never has, never will.
Super annoying that the message when you're trying to do it inside of SUMIFS isn't so clear.
All right. Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
