Excel - How To Get Sum From Multiple Sheets In Excel Using 3D Reference - Episode 1984

แชร์
ฝัง
  • เผยแพร่เมื่อ 21 ธ.ค. 2024

ความคิดเห็น • 31

  • @excelisfun
    @excelisfun 8 ปีที่แล้ว

    Thanks for the amazing book and the great daily videos! I love it!!! : )

  • @sodethsun6149
    @sodethsun6149 2 ปีที่แล้ว

    Thanks for the video it helps me a lot.

  • @iffkitchen941
    @iffkitchen941 ปีที่แล้ว

    Great video worked for me first attempt thank you

  • @Sal_A
    @Sal_A 8 ปีที่แล้ว

    Nice trick to SUM worksheets with wildcard. Thanks!

    • @excelisfun
      @excelisfun 8 ปีที่แล้ว

      I agree - that is a very unusual and amazing * trick!!!

  • @shivashankardshivu7821
    @shivashankardshivu7821 2 ปีที่แล้ว

    Use full thanks

  • @kapamagicman
    @kapamagicman 8 ปีที่แล้ว

    Wow...amazing . Have to try this!

  • @serarakavono9255
    @serarakavono9255 3 ปีที่แล้ว

    Is it possible to still carry out this summation successfully if the table formats are not consistent throughout the sheets in the workbook ?????

    • @MrXL
      @MrXL  3 ปีที่แล้ว

      The method shown in this video requires the sheets to be set up the same.

  • @SuperRajeshnair
    @SuperRajeshnair 2 ปีที่แล้ว

    What if the values cell A values are shuffled in each sheet, how to find the matching text and add values?

    • @MrXL
      @MrXL  2 ปีที่แล้ว

      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.

  • @MrArafat0
    @MrArafat0 2 ปีที่แล้ว

    Hello Sir if i want to make all sheet a summery on a Sheet all month total amount separately. how can i do it?

  • @Explore_Your_Choice
    @Explore_Your_Choice 4 ปีที่แล้ว

    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?

  • @optimisationbyamy
    @optimisationbyamy 3 ปีที่แล้ว

    Any idea if there is an equivalent for Google Sheets?

    • @MrXL
      @MrXL  3 ปีที่แล้ว

      No idea. Check Leila Gharani's channel. She has some Google Sheets content.

  • @rebazosman1240
    @rebazosman1240 ปีที่แล้ว

    hello sir if we have different cell in different sheet how can i sum?

    • @MrXL
      @MrXL  ปีที่แล้ว

      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

  • @rosiecaballero2158
    @rosiecaballero2158 2 ปีที่แล้ว

    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.

    • @MrXL
      @MrXL  2 ปีที่แล้ว

      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

  • @marshalleastin2003
    @marshalleastin2003 3 ปีที่แล้ว

    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?

    • @MrXL
      @MrXL  3 ปีที่แล้ว +1

      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

  • @1odham
    @1odham ปีที่แล้ว

    This is assuming that all of the totals to be summed up are in the same cell. What if the cells are different?

    • @MrXL
      @MrXL  ปีที่แล้ว

      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.

    • @1odham
      @1odham ปีที่แล้ว

      @@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.

    • @MrXL
      @MrXL  ปีที่แล้ว

      @@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.

    • @1odham
      @1odham ปีที่แล้ว

      @@MrXL yes. But there are several daily totals. Could I send you a screen shot? Or the file?

    • @1odham
      @1odham ปีที่แล้ว

      Because I will have to do this with all twelve months.

  • @w.tibbsclemens636
    @w.tibbsclemens636 3 ปีที่แล้ว +1

    You sound like an airline pilot

  • @Explore_Your_Choice
    @Explore_Your_Choice 4 ปีที่แล้ว

    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.

  • @PAMSAAM
    @PAMSAAM 3 ปีที่แล้ว

    This made absolutely no sense!