Here is a video on how to combine everything in memory. th-cam.com/video/VG6EDm2Ma9g/w-d-xo.html. You could add steps to Group By column A and Sun everything else.
Thanks for your prompt response but the query is not sufficed. Look I have one distributor under it i have a three different months and then by per month i have two columns one for retail and second for wholesale. now look column, shape i.e., 'Retail'Wholesale''Retail'Wholesale''Retail'Wholesale' this is appearing month wise like "May"June"July" under one distributor now these six columns are being repeated 54 times left to right. In the right end I need sum of 54 distributors for the month of May "Retail" only as grand total. and I don't want to apply this method like =E10+K10+Q10. what should I do to minimize this practice?
okay, so I followed the instructions and created my formula in excel. Now the problem is that if I delete the numbers my formula disappears. I am not a computer person by any means so any help is appreciated.
Hi Rosie After you have the formula working, but before you delete the numbers, you want to Copy the formula cells and Paste Values. Here is a 60-second demo: th-cam.com/video/_ZUmaf9ZgLg/w-d-xo.html
I have a question that I haven't been able to find. I work in a call center and I keep track of my own calls on an excell sheet so I can make sure I am meeting my goals. Have one file for the whole month and I have a sheet tab for each day I work. Right now what I do is set up for day one of the month (with a blank template that I have) I zero it out. What I keep track of is the results of each call as well as the total number of calls. I have three categories for results of a call that I have my daily totals on each sheet. Then the next day I copy and move to end the previous sheet and zero it out and fill it in for that day. I know how to get a monthly total at the end of the month. But what I would like to do is have it set up so that I can always see my month to date. Without having to reset it up every time and just copy it over. Is there a way to do that?
The whole trick to this is to put your totals at the TOP of the worksheet. Maybe you have date in A1 and then in row 3, you could have something like =COUNT(A5:A200) and =SUM(J5:J200). The reason you put the totals at the top is so you know the totals are always going to be in row 3. If you do the natural thing and put the totals at the bottom, then some days they will be in row 12 and other days they will be in row 82, and then it is a pain to total them. The next thing that I want you to do is to insert a sheet to the left of your first sheet of the month. Rename that sheet to be Start. Put a sheet to the right of your last sheet of the month and call it End. Then, put a sheet to the right of End. Call that sheet Totals. The formula on Totals is a *spearing* formula in that it spears through 33 sheets, grabbing J3 from each sheet: =SUM(Start:End!J3). I have done this in half a dozen videos. Here is one: th-cam.com/video/1ftMg49mlEo/w-d-xo.html
It becomes far more difficult when they aren't lined up. Do you have the headings in the same row on each worksheet? You could combine all the sheets with VBA using this method: th-cam.com/video/n9WcP18wXFA/w-d-xo.html But the better way is to virtually combine all the sheets in memory using the Get & Transform tools on the Data tab. This video walks you through it: th-cam.com/video/VG6EDm2Ma9g/w-d-xo.html Once you get a virtual grid with all of the data across all worksheets, you can use the Power Query editor to Group by a field and Sum all records across all worksheets. This is a complex set of steps, but once you get it set up, it would work great. If you want to share details about your data set, send me an e-mail. Click on the "About" link in my profile and you can get my e-mail address.
@@MrXL what I’m doing is tracking the days output per hour per day per month on one tab. All of the days are the same. But, using overages we find on the floor, that would be in row 27. Then each day would have a different cell, such as T, then TA, QR, etc. I would like an easy way other than =SUM( and then having to click each cell for the formula.
@@1odham Is there some heading in T, TA, QR that tells you this is an item that you would want to include? For example, does row 4 say "Daily Total" for the columns that should be included? If so, you could do =SUMIFS(Sheet1!A27:XFD27,Sheet1!A4:XFD4,"Daily Total") This would add up the relevant cells on Sheet1. But it sounds like you have multiple sheets. I would add a new Summary worksheet. In A3, put a list of the worksheets. Maybe the worksheet names are 'Week One' in A3 'Week Two' in A4 'Week Three' in A5 'Week Four' in A6 If Summary!A3 says "Week One" then you could have a cell in Summary!B3 that would do the same SUMIFS: =SUMIFS(INDIRECT(A3&"!A27:XFD27"),INDIRECT(A3&"!A4:XFD4"),"Daily Total") The INDIRECT function takes something that looks like a valid cell address 'Week One'!A27:XFD27 and converts it to a cell address. The advantage here is that you can drag your B3 formula down to all of the other rows and it would point to the next sheet name. It is tough to explain this in hypothetical terms... without seeing the actual worksheet.
Dear Mr. Excel I am your subscriber and found very satisfactory solutions by watching your uploaded videos. I am in trouble reference podcast 1984 the method I want to apply is same but I need sum on the same sheet I mean I have all my data left to right in one sheet and in the right end I want the sum results. I have applied your given solution but failed. Please help me to fix it.
Thanks for the amazing book and the great daily videos! I love it!!! : )
Thanks for the video it helps me a lot.
Great video worked for me first attempt thank you
Nice trick to SUM worksheets with wildcard. Thanks!
I agree - that is a very unusual and amazing * trick!!!
Use full thanks
Wow...amazing . Have to try this!
Is it possible to still carry out this summation successfully if the table formats are not consistent throughout the sheets in the workbook ?????
The method shown in this video requires the sheets to be set up the same.
What if the values cell A values are shuffled in each sheet, how to find the matching text and add values?
Here is a video on how to combine everything in memory. th-cam.com/video/VG6EDm2Ma9g/w-d-xo.html.
You could add steps to Group By column A and Sun everything else.
Hello Sir if i want to make all sheet a summery on a Sheet all month total amount separately. how can i do it?
Thanks for your prompt response but the query is not sufficed.
Look
I have one distributor under it i have a three different months and then by per month i have two columns one for retail and second for wholesale. now look column, shape i.e., 'Retail'Wholesale''Retail'Wholesale''Retail'Wholesale' this is appearing month wise like "May"June"July" under one distributor now these six columns are being repeated 54 times left to right. In the right end I need sum of 54 distributors for the month of May "Retail" only as grand total. and I don't want to apply this method like =E10+K10+Q10. what should I do to minimize this practice?
Any idea if there is an equivalent for Google Sheets?
No idea. Check Leila Gharani's channel. She has some Google Sheets content.
hello sir if we have different cell in different sheet how can i sum?
Here is a video showing how to combine data from worksheets where the rows do not line up: th-cam.com/video/UO11Ase1_Ys/w-d-xo.html
okay, so I followed the instructions and created my formula in excel. Now the problem is that if I delete the numbers my formula disappears. I am not a computer person by any means so any help is appreciated.
Hi Rosie
After you have the formula working, but before you delete the numbers, you want to Copy the formula cells and Paste Values. Here is a 60-second demo: th-cam.com/video/_ZUmaf9ZgLg/w-d-xo.html
I have a question that I haven't been able to find. I work in a call center and I keep track of my own calls on an excell sheet so I can make sure I am meeting my goals. Have one file for the whole month and I have a sheet tab for each day I work. Right now what I do is set up for day one of the month (with a blank template that I have) I zero it out. What I keep track of is the results of each call as well as the total number of calls. I have three categories for results of a call that I have my daily totals on each sheet. Then the next day I copy and move to end the previous sheet and zero it out and fill it in for that day. I know how to get a monthly total at the end of the month. But what I would like to do is have it set up so that I can always see my month to date. Without having to reset it up every time and just copy it over. Is there a way to do that?
The whole trick to this is to put your totals at the TOP of the worksheet. Maybe you have date in A1 and then in row 3, you could have something like =COUNT(A5:A200) and =SUM(J5:J200). The reason you put the totals at the top is so you know the totals are always going to be in row 3. If you do the natural thing and put the totals at the bottom, then some days they will be in row 12 and other days they will be in row 82, and then it is a pain to total them.
The next thing that I want you to do is to insert a sheet to the left of your first sheet of the month. Rename that sheet to be Start.
Put a sheet to the right of your last sheet of the month and call it End.
Then, put a sheet to the right of End. Call that sheet Totals.
The formula on Totals is a *spearing* formula in that it spears through 33 sheets, grabbing J3 from each sheet: =SUM(Start:End!J3).
I have done this in half a dozen videos. Here is one: th-cam.com/video/1ftMg49mlEo/w-d-xo.html
This is assuming that all of the totals to be summed up are in the same cell. What if the cells are different?
It becomes far more difficult when they aren't lined up.
Do you have the headings in the same row on each worksheet?
You could combine all the sheets with VBA using this method: th-cam.com/video/n9WcP18wXFA/w-d-xo.html
But the better way is to virtually combine all the sheets in memory using the Get & Transform tools on the Data tab. This video walks you through it: th-cam.com/video/VG6EDm2Ma9g/w-d-xo.html
Once you get a virtual grid with all of the data across all worksheets, you can use the Power Query editor to Group by a field and Sum all records across all worksheets.
This is a complex set of steps, but once you get it set up, it would work great. If you want to share details about your data set, send me an e-mail. Click on the "About" link in my profile and you can get my e-mail address.
@@MrXL what I’m doing is tracking the days output per hour per day per month on one tab. All of the days are the same. But, using overages we find on the floor, that would be in row 27. Then each day would have a different cell, such as T, then TA, QR, etc. I would like an easy way other than =SUM( and then having to click each cell for the formula.
@@1odham Is there some heading in T, TA, QR that tells you this is an item that you would want to include? For example, does row 4 say "Daily Total" for the columns that should be included?
If so, you could do =SUMIFS(Sheet1!A27:XFD27,Sheet1!A4:XFD4,"Daily Total")
This would add up the relevant cells on Sheet1.
But it sounds like you have multiple sheets.
I would add a new Summary worksheet. In A3, put a list of the worksheets.
Maybe the worksheet names are
'Week One' in A3
'Week Two' in A4
'Week Three' in A5
'Week Four' in A6
If Summary!A3 says "Week One" then you could have a cell in Summary!B3 that would do the same SUMIFS:
=SUMIFS(INDIRECT(A3&"!A27:XFD27"),INDIRECT(A3&"!A4:XFD4"),"Daily Total")
The INDIRECT function takes something that looks like a valid cell address 'Week One'!A27:XFD27 and converts it to a cell address.
The advantage here is that you can drag your B3 formula down to all of the other rows and it would point to the next sheet name.
It is tough to explain this in hypothetical terms... without seeing the actual worksheet.
@@MrXL yes. But there are several daily totals. Could I send you a screen shot? Or the file?
Because I will have to do this with all twelve months.
You sound like an airline pilot
Dear Mr. Excel
I am your subscriber and found very satisfactory solutions by watching your uploaded videos. I am in trouble reference podcast 1984 the method I want to apply is same but I need sum on the same sheet I mean I have all my data left to right in one sheet and in the right end I want the sum results. I have applied your given solution but failed. Please help me to fix it.
This made absolutely no sense!