Microsoft Excel Tutorial: Sum across worksheets when rows do not line up?
Today's question from Rebaz on podcast 1984 - Excel Sum Across Worksheets: "if we have different cell in different sheet, how can I Sum?"
Download the workbook from today: Excel Sum Across Sheets When Rows Do Not Line Up - Episode 2622 Sample Files - MrExcel Publishing
This video shows you an easy way to build a 3-D reference in Excel, also known as a spearing formula. Excel functions include SUM, XLOOKUP, FILTER, SUMIFS, TEXTJOIN, TEXTSPLIT, SUMPRODUCT, Helper Arrays, LET, Python in Excel, and VSTACK.
Table of Contents
(0:00) Problem: Adding Across Sheets that are not lined up
(0:43) How to build a 3-D Reference
(1:19) Does XLOOKUP work with 3-D?
(1:50) Does FILTER work with 3-D?
(2:09) Does SUMIFS work with 3-D?
(2:38) TEXTJOIN works with 3-D References
(3:23) TEXTSPLIT of TEXTJOIN
(3:48) Building out TEXTJOIN solution
(5:36) Using Python in Excel Fails with a Formula
(6:01) VSTACK works with 3-D
(6:45) Could use Power Query
(7:04) How would you do this?
(7:09) Is there a list of Excel functions that work with 3-D?
(7:36) Wrap-up / Nancy Faust
(7:45) Please subscribe
Today's question from Rebaz on podcast 1984 - Excel Sum Across Worksheets: "if we have different cell in different sheet, how can I Sum?"
Download the workbook from today: Excel Sum Across Sheets When Rows Do Not Line Up - Episode 2622 Sample Files - MrExcel Publishing
This video shows you an easy way to build a 3-D reference in Excel, also known as a spearing formula. Excel functions include SUM, XLOOKUP, FILTER, SUMIFS, TEXTJOIN, TEXTSPLIT, SUMPRODUCT, Helper Arrays, LET, Python in Excel, and VSTACK.
Table of Contents
(0:00) Problem: Adding Across Sheets that are not lined up
(0:43) How to build a 3-D Reference
(1:19) Does XLOOKUP work with 3-D?
(1:50) Does FILTER work with 3-D?
(2:09) Does SUMIFS work with 3-D?
(2:38) TEXTJOIN works with 3-D References
(3:23) TEXTSPLIT of TEXTJOIN
(3:48) Building out TEXTJOIN solution
(5:36) Using Python in Excel Fails with a Formula
(6:01) VSTACK works with 3-D
(6:45) Could use Power Query
(7:04) How would you do this?
(7:09) Is there a list of Excel functions that work with 3-D?
(7:36) Wrap-up / Nancy Faust
(7:45) Please subscribe
Transcript of the video:
Here's a great one.
How are we going to sum across sheets when the rows are not lined up?
Or which Excel functions really work with 3-D references?
Great question this morning from Rebaz on a video called Sum Across Worksheets.
"Hello, sir.
If we have different cell in different sheet, how can I sum?" All right, so back in episode 1984 it was simple.
There was Andy through Nan. They were in the same order.
And we were going to spear through the sheets, Apple, Banana, Cherry, Dill.
But now, since each sheet is sorted high to low by sales.
We can't count on the fact that Andy is always going to be in A2.
He's going to be in a different place each time.
All right, so we're going to start out with just the simplest 3-D formula here.
So I want to equal SUM, and I want to sum everything from Apple to Fig.
So watch how cool this is. I click on Apple but I don't click on anything.
And then I shift-click on the Fig sheet. And then select the cells that I want.
Close paren. And press Enter.
And that formula spears through and grabs everything from B2 to B15 on every sheet from Apple to Fig. Simple enough, right?
So then I'm thinking, all right, how are we going to be able to do this where we don't know where Andy is on each sheet? And my first thought of course is XLOOKUP, right?
So a simple little XLOOKUP. Go look for Andy on the Apple sheet.
Return the revenue from the corresponding row in column B.
That works great.
But then when I try and make XLOOKUP work from Apple to Fig, Apple to Fig.
And send the whole thing into the SUM function, that does not work. So XLOOKUP is not working with 3-D references.
Then I thought, okay, how about FILTER? FILTER'S kind of a new formula.
What if we FILTER on the Apple sheet.
To get everything where the column A is equal to Andy? And that works great for one sheet.
But when I try and do it for all of the sheets, it fails.
SUMIFS. I mean, boy, this is almost a no-brainer.
This one should really work. Go Sum up everything in column B on Apple.
Where column A is equal to C4, right?
And when I tried to make that into a 3-D reference.
Everything from Apple to Fig, I get a #VALUE! error. So this is kind of driving me crazy now.
What functions really work with 3-D references? And I remember one of the MVP summits, Joe McDaid.
TEXTJOIN had just come out. And he was really proud of the fact.
He said, "Hey, yeah, TEXTJOIN is going to work with a 3-D reference".
So I knew that that is possible for TEXTJOIN to work with a 3-D reference.
So check this out, TEXTJOIN. The delimiter is going to be a pipe.
Because I figured there wouldn't be a pipe in the data.
Ignore empty, I said False.
And then I want everything from Apple to Fig, A2 to A15, right?
And what it does is it gives me one huge long string of every name on every sheet. And so it's Jared, Ike, Kelly.
Jared, Ike, Kelly. Yeah, see it's doing it.
And now, thankfully, we're at the point where we have TEXTSPLIT.
So I can take the TEXTSPLIT of the TEXTJOIN and split it out.
This is the column delimiter. I left that blank.
Into a row.
So now I have a way that I can take all of the data from all of those sheets.
Put it into one very long string.
And then unwind that right here into this sheet with TEXTSPLIT. All right, so using the same concept we just did.
I get a list of all the names. And then I get a list of all of the sales amounts.
So I'm bringing everything from the other sheets into the sheet, the names and then the amounts.
Here's the list of our people arranged alphabetically.
And if we can just reuse those Helper arrays. I ended up using SUMPRODUCT here, which is funny.
Historically, I was always the last person to use SUMPRODUCT.
But it's definitely going to work in this case.
So we're going to go look at every single item in the C reference and see if it's equal to Andy. That's either going to be True or False.
And when we multiply a True or False by the revenue in the D6 array.
The Trues are going to change to one and the Falses change to zero.
So it ends up basically being like a SUMIF almost, and it gets our total.
So we have one formula there. We have to copy it down.
That's we're using the Helper arrays.
If you didn't want to have the Helper arrays exposed here.
Then you could actually put those Helper arrays right inside. So the SUMPRODUCT here's our first parentheses.
So we go basically use TEXTJOIN to put everything together from column A.
And then split it back out. And see if it's equal to Andy.
And then multiply that by all of the revenue.
We put it all together with TEXTJOIN and then split it back out. I also took a look at using LET.
Is this any simpler? I don't think it's much simpler.
I don't know. I guess that's your call.
And then I started thinking, okay, what else could make this easier?
I've just done a whole bunch of episodes on Python.
Is there some way to store that first formula, that first Helper array in a Python object?
But unfortunately it looks like the =XL() function only points to a reference.
We can't put a formula inside of there.
So building all this out with Python just gives me a whole bunch of Python errors. And then the other thing I thought of.
All right, so Joe McDaid made a big deal about TEXTJOIN, being able to work with 3-D references.
What else has Joe given us? What's in the Joe McDaid era that might work?
And I thought about VSTACK, right? Would VSTACK work?
So everything from Apple to Fig. Get me A2 to A15.
Everything from Apple to Fig get me B2 to B15. And the same thing here using the Helper array.
Or just one formula and that formula probably is not horrible.
So if I had to do this with a formula, I think I'm probably going to go with this VSTACK function.
Provided I'm on a modern Excel, Microsoft 365 and I have the VSTACK function.
You could also do this with Power Query. There's a great Matt Allington video out there.
That I probably have stolen and put on my channel somewhere.
That would allow us to unwind that data in Power Query and sum it up.
And so that's definitely a great way to go.
I'm also curious, if anyone watching has a better way.
And boy I would really love a website somewhere that goes through the 500 functions in Excel and which ones actually work as 3-D references and which ones don't.
We know that the SUM function does. And now TEXTJOIN and VSTACK both work.
But unfortunately FILTER XLOOKUP and SUMIFS do not.
It'd be nice if there was a comprehensive list somewhere of which ones will work with 3-D arrays.
All right, what a great question sent in this morning by Rebaz.
Thanks for sending that question in and thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
How are we going to sum across sheets when the rows are not lined up?
Or which Excel functions really work with 3-D references?
Great question this morning from Rebaz on a video called Sum Across Worksheets.
"Hello, sir.
If we have different cell in different sheet, how can I sum?" All right, so back in episode 1984 it was simple.
There was Andy through Nan. They were in the same order.
And we were going to spear through the sheets, Apple, Banana, Cherry, Dill.
But now, since each sheet is sorted high to low by sales.
We can't count on the fact that Andy is always going to be in A2.
He's going to be in a different place each time.
All right, so we're going to start out with just the simplest 3-D formula here.
So I want to equal SUM, and I want to sum everything from Apple to Fig.
So watch how cool this is. I click on Apple but I don't click on anything.
And then I shift-click on the Fig sheet. And then select the cells that I want.
Close paren. And press Enter.
And that formula spears through and grabs everything from B2 to B15 on every sheet from Apple to Fig. Simple enough, right?
So then I'm thinking, all right, how are we going to be able to do this where we don't know where Andy is on each sheet? And my first thought of course is XLOOKUP, right?
So a simple little XLOOKUP. Go look for Andy on the Apple sheet.
Return the revenue from the corresponding row in column B.
That works great.
But then when I try and make XLOOKUP work from Apple to Fig, Apple to Fig.
And send the whole thing into the SUM function, that does not work. So XLOOKUP is not working with 3-D references.
Then I thought, okay, how about FILTER? FILTER'S kind of a new formula.
What if we FILTER on the Apple sheet.
To get everything where the column A is equal to Andy? And that works great for one sheet.
But when I try and do it for all of the sheets, it fails.
SUMIFS. I mean, boy, this is almost a no-brainer.
This one should really work. Go Sum up everything in column B on Apple.
Where column A is equal to C4, right?
And when I tried to make that into a 3-D reference.
Everything from Apple to Fig, I get a #VALUE! error. So this is kind of driving me crazy now.
What functions really work with 3-D references? And I remember one of the MVP summits, Joe McDaid.
TEXTJOIN had just come out. And he was really proud of the fact.
He said, "Hey, yeah, TEXTJOIN is going to work with a 3-D reference".
So I knew that that is possible for TEXTJOIN to work with a 3-D reference.
So check this out, TEXTJOIN. The delimiter is going to be a pipe.
Because I figured there wouldn't be a pipe in the data.
Ignore empty, I said False.
And then I want everything from Apple to Fig, A2 to A15, right?
And what it does is it gives me one huge long string of every name on every sheet. And so it's Jared, Ike, Kelly.
Jared, Ike, Kelly. Yeah, see it's doing it.
And now, thankfully, we're at the point where we have TEXTSPLIT.
So I can take the TEXTSPLIT of the TEXTJOIN and split it out.
This is the column delimiter. I left that blank.
Into a row.
So now I have a way that I can take all of the data from all of those sheets.
Put it into one very long string.
And then unwind that right here into this sheet with TEXTSPLIT. All right, so using the same concept we just did.
I get a list of all the names. And then I get a list of all of the sales amounts.
So I'm bringing everything from the other sheets into the sheet, the names and then the amounts.
Here's the list of our people arranged alphabetically.
And if we can just reuse those Helper arrays. I ended up using SUMPRODUCT here, which is funny.
Historically, I was always the last person to use SUMPRODUCT.
But it's definitely going to work in this case.
So we're going to go look at every single item in the C reference and see if it's equal to Andy. That's either going to be True or False.
And when we multiply a True or False by the revenue in the D6 array.
The Trues are going to change to one and the Falses change to zero.
So it ends up basically being like a SUMIF almost, and it gets our total.
So we have one formula there. We have to copy it down.
That's we're using the Helper arrays.
If you didn't want to have the Helper arrays exposed here.
Then you could actually put those Helper arrays right inside. So the SUMPRODUCT here's our first parentheses.
So we go basically use TEXTJOIN to put everything together from column A.
And then split it back out. And see if it's equal to Andy.
And then multiply that by all of the revenue.
We put it all together with TEXTJOIN and then split it back out. I also took a look at using LET.
Is this any simpler? I don't think it's much simpler.
I don't know. I guess that's your call.
And then I started thinking, okay, what else could make this easier?
I've just done a whole bunch of episodes on Python.
Is there some way to store that first formula, that first Helper array in a Python object?
But unfortunately it looks like the =XL() function only points to a reference.
We can't put a formula inside of there.
So building all this out with Python just gives me a whole bunch of Python errors. And then the other thing I thought of.
All right, so Joe McDaid made a big deal about TEXTJOIN, being able to work with 3-D references.
What else has Joe given us? What's in the Joe McDaid era that might work?
And I thought about VSTACK, right? Would VSTACK work?
So everything from Apple to Fig. Get me A2 to A15.
Everything from Apple to Fig get me B2 to B15. And the same thing here using the Helper array.
Or just one formula and that formula probably is not horrible.
So if I had to do this with a formula, I think I'm probably going to go with this VSTACK function.
Provided I'm on a modern Excel, Microsoft 365 and I have the VSTACK function.
You could also do this with Power Query. There's a great Matt Allington video out there.
That I probably have stolen and put on my channel somewhere.
That would allow us to unwind that data in Power Query and sum it up.
And so that's definitely a great way to go.
I'm also curious, if anyone watching has a better way.
And boy I would really love a website somewhere that goes through the 500 functions in Excel and which ones actually work as 3-D references and which ones don't.
We know that the SUM function does. And now TEXTJOIN and VSTACK both work.
But unfortunately FILTER XLOOKUP and SUMIFS do not.
It'd be nice if there was a comprehensive list somewhere of which ones will work with 3-D arrays.
All right, what a great question sent in this morning by Rebaz.
Thanks for sending that question in and thanks to you for stopping by.
We'll see you next time for another Netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
