Excel Sum Across Sheets When Rows Do Not Line Up - Episode 2622

Подписаться 155 тыс.

Просмотров 8 тыс.

50% 1

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: www.mrexcel.com/youtube/UO11A...
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.
Welcome to episode 2622 of the MrExcel Netcast. Today, we have a great question from Rebaz about summing across sheets when the rows are not lined up. This can be a tricky task, but fear not, we have some solutions for you.
In the past, it was simple to sum across sheets when the rows were in the same order. However, with each sheet now being sorted differently, we can't rely on the fact that the data will be in the same place each time. So, we need to find a way to sum across sheets without knowing the exact location of the data.
We start with a simple 3-D formula, using the SUM function. By clicking on the first sheet and then shift-clicking on the last sheet, we can select the cells we want to sum. However, this method may not work for all functions. So, we explore other options such as XLOOKUP, FILTER, and SUMIFS, but unfortunately, these do not work with 3-D references.
But fear not, there are still some functions that do work with 3-D references. One of them is TEXTJOIN, which can combine data from multiple sheets into one long string. We can then use TEXTSPLIT to split this string and get the data we need. Another option is VSTACK, which allows us to stack data from multiple sheets into one array.
If you're using Microsoft 365, you can also use Power Query to sum across sheets. And if you have any other suggestions or solutions, please share them in the comments below. It would also be great to have a comprehensive list of functions that work with 3-D references, so if anyone knows of one, please share it with us.
Thank you for tuning in to this episode of the MrExcel Netcast. If you enjoyed this video, please like, subscribe, and ring the bell to be notified of future episodes. And don't forget to leave your questions and comments down below. See you next time!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
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:14) Is there a list of Excel functions that work with 3-D?
(7:33) Wrap-up / Nancy Faust
(7:43) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Excel 3-D References
FILTER function and 3-D references
How to sum cells from different sheets
LET function in Excel
Sum Across Sheets Excel
SUMIFS and 3-D references
SUMPRODUCT with 3-D references
TEXTJOIN with 3-D references
TEXTSPLIT function in Excel
Using Helper arrays in Excel
VSTACK function in Excel
XLOOKUP not working with 3-D references
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 19

@Excelambda 10 месяцев назад

Great video!! Single cell formula =LET(a,VSTACK(Apple:Fig!A2:B15),n,INDEX(a,,1),v,INDEX(a,,2),u,SORT(UNIQUE(n)),HSTACK(u,MAP(u,LAMBDA(x,SUM((x=n)*v))))) - 3D reference only once - names lists (1st col) could be all different (not only same list in a diff order)

@GeertDelmulle 10 месяцев назад

Hey friend (long time no C ;-), Why use SUM with a large argument array operation and not just FILTER (in the MAP-LAMBDA)?

@johndurran614 10 месяцев назад

That would have been a good example to use in Dueling Excel with Mike Girvan

@billhladik406 10 месяцев назад

TOROW() and TOCOL() also do 3D refs. BYROW or MMULT can create a matrix of summed values for each name from sheet1. =LET(list,Sheet1!A1:A3, a,TOCOL(Sheet1:Sheet3!A1:A3), b,TOROW(Sheet1:Sheet3!B1:B3), c,BYROW(list,LAMBDA(r,SUM(IF(TRANSPOSE(a)=r,b,0)))), HSTACK(list,c)) or =LET(list,Sheet1!A1:A3, a,TOCOL(Sheet1:Sheet3!A1:A3), b,TOROW(Sheet1:Sheet3!B1:B3), c,MMULT(IF(TRANSPOSE(a)=list,b,0),SEQUENCE(COLUMNS(b),,1,0)), HSTACK(list,c))

@vosk875 10 месяцев назад

Great challenge. Here's a one cell solution incorporating vstack and choosecols. this also accepts the filter fx. I21 simply refers to a sorted unique list of names. If you want it to spill you can easily include a byrow/map-lambda fx. =SUM(CHOOSECOLS(LET(vs,VSTACK(Apple:Fig!$A$2:$B$15),FILTER(vs,CHOOSECOLS(vs,1)=I21)),2))

@GeertDelmulle 10 месяцев назад

Hey Mr.Excel, VStack is really the way to go, and the TEXT functions not (alas - the number of characters in 1 string is too restrictive for larger data sets). Here's my take on it - it uses FILTER: =LET(array,VSTACK(Apple:Fig!A2:B50), names,INDEX(array,,1), headers,DROP(SORT(UNIQUE(names)),-1), sums,MAP(headers,LAMBDA(x,SUM(FILTER(array,names=x)))), HSTACK(headers,sums) )

@GeertDelmulle 10 месяцев назад

NB in PQ-M it is really simpel: let Source = Excel.Workbook(File.Contents("SourceFile.xlsx"), true, true), FilterRows = Table.SelectRows(Source, each ([Item] = "Apple" or [Item] = "Banana" or [Item] = "Cherry" or [Item] = "Dill" or [Item] = "Eggplant" or [Item] = "Fig")), VStackTables = Table.Combine(FilterRows[Data]), GroupRows = Table.Group(VStackTables, {"Name"}, {{"Sum", each List.Sum([Sales]), type number}}), SortRows = Table.Sort(GroupRows,{{"Name", Order.Ascending}}) in SortRows

@dropmeshort 10 месяцев назад

Would the old Consolidate tool in Excel work for this example? Thanks for this tutorial on 3-D references @MrXL.

@MrXL 10 месяцев назад

Yes!

@ExcelMoments 10 месяцев назад

In one cell, one could it this way =LET(a,VSTACK(Apple:Fig!A2:B15), b,CHOOSECOLS(a,1), c,UNIQUE(b), SORT(HSTACK(c,MAP(c,LAMBDA(x,SUM(FILTER(CHOOSECOLS(a,2),b=x))))),2,1)) The backbone being of course the VSTACK that works with 3D references - INDEX can also replace CHOOSECOLS

@Mixolydian74 10 месяцев назад

does VSTACK still work if the amount of rows per sheet varies? This would imply that the same name appears more than once in any one of the sheets but has different values

@MrXL 10 месяцев назад

Yes … it sums all Andy on all sheets.

@GeertDelmulle 10 месяцев назад

Sure, just make your selection big enough - see my solution in the comments.

@jakerubo 10 месяцев назад

Does running unique() across the 3D array fail as well?

@johnnymac7395 10 месяцев назад

Would a lambda function work?

@christoslefkimiotis9889 10 месяцев назад

......or, just with a helper column in every sheet, rebuild the sort according to the Apple sheet and do a 3d sum.......

@jamijami7586 10 месяцев назад

How to get a 6 digit lottery number in between 1 to 29 ?. Pls tell me

@DesertFly3 10 месяцев назад

I came up with this for Python. I would argue it's less complicated than the formula solutions: apple = xl("Apple!A1:B15", headers=True) banana = xl("Banana!A1:B15", headers=True) cherry = xl("Cherry!A1:B15", headers=True) dill = xl("Dill!A1:B15", headers=True) eggplant = xl("Eggplant!A1:B15", headers=True) fig = xl("Fig!A1:B15", headers=True) all_fruits = pd.concat([apple, banana, cherry, dill, eggplant, fig]) all_fruits.groupby("Name")["Sales"].sum().reset_index()