Excel Magic Trick 1024: SUMIF & SUMIFS function & Mixed Cell References & Dynamic Table Range

Поделиться
HTML-код
  • Опубликовано: 23 янв 2025

Комментарии • 88

  • @excelisfun
    @excelisfun  11 лет назад +2

    Just a guy having fun with Excel!
    I am glad that you like the video!

  • @excelisfun
    @excelisfun  11 лет назад +1

    That Table feature is annoying...
    Glad you like the video!
    Thanks for your support in buying the book!

  • @excelisfun
    @excelisfun  11 лет назад +1

    Great tip about formulas across sheets!!

  • @lazyorbit86
    @lazyorbit86 9 лет назад +4

    i love how he talks about topics he's taught before like it's a brand new lesson.
    not like other people who would just say, "hey i taught this before, go look at that other 20-minute video for this one trick"

    • @excelisfun
      @excelisfun  9 лет назад

      +lazyorbit86 , Yes, that is fundamental to my approach - every new video is an awesome new experience of trying to help people have fun with Excel!!!

  • @excelisfun
    @excelisfun  11 лет назад +1

    Thanks for watching, krn14242!

  • @excelisfun
    @excelisfun  11 лет назад +1

    You are welcome!

  • @excelisfun
    @excelisfun  11 лет назад +1

    That is a hot tip! I will make a video to pass along your hot tip! Thanks!

  • @excelisfun
    @excelisfun  11 лет назад +1

    Nice tip! EMT 1023 will be out in one or two days.

  • @lazyorbit86
    @lazyorbit86 9 лет назад +3

    i never knew that the absolute cell references would be dynamic if you're referring to a table.
    THIS VIDEO CHANGED MY LIFE.

    • @excelisfun
      @excelisfun  9 лет назад

      +lazyorbit86 Glad you liked the video!!! :)

  • @excelisfun
    @excelisfun  11 лет назад +1

    If the table is on one sheet and the formula is on a different sheet, use the Table Formula Nomenclature, not normal cell references. This way the formula will be dynamic and update when new records are added.

  • @excelisfun
    @excelisfun  11 лет назад +1

    Awesome!

  • @90kay
    @90kay 6 лет назад +2

    Thank you so much !!!!! You have shared all data for studying, you are so generous !!!!
    I am big fan of you.

  • @krn14242
    @krn14242 11 лет назад

    Cool trick. Never thought of selected range -1 to avoid the table nomenclature. Thanks Mike

  • @msifre5914
    @msifre5914 4 года назад

    thanks for all your lessons. I learned to love Excel because of you and it has helped me so much in my professional life. Now I help other. Thanks

    • @excelisfun
      @excelisfun  4 года назад

      That is awesome!!! You are playing it forward : ) : )

  • @fernando5166
    @fernando5166 Год назад

    We continue learning, thank you very much for your teachings

  • @anil11996
    @anil11996 11 лет назад +1

    You are magician of excel

  • @learntoearn8533
    @learntoearn8533 4 года назад

    Thank you very much.... After so many videos I found what I want.

  • @excelisfun
    @excelisfun  11 лет назад

    For back and forth dialog to get Excel solutions try THE best Excel question site:
    mrexcel [dot] com/forum

  • @rozianarashid9105
    @rozianarashid9105 6 лет назад

    This is exactly what i am looking for. Tq for making my job more effective.

    • @excelisfun
      @excelisfun  6 лет назад

      You are welcome for the More Effective!!! Thanks for the support, Roziana, with your comments, Thumbs Ups and Sub : )

  • @TheMesoPeaks
    @TheMesoPeaks 8 лет назад

    I love this channel.
    Knowledge is just overwhelmingly good!

    • @excelisfun
      @excelisfun  8 лет назад +1

      +TheMesoPeaks Glad the knowledge helps!

    • @TheMesoPeaks
      @TheMesoPeaks 8 лет назад

      Yep...I need all of them at work : )

  • @sanjayprajapat
    @sanjayprajapat 8 лет назад

    Great Video....... clears every thing.

  • @kimyenchu
    @kimyenchu 11 месяцев назад

    Hello Mike, do you have a video that shows sumifs and countifs across multiple columns in different sheets? The column names are the same but table names are different and there is 1 table on each sheet. The tables in sheet 2 has one extra column so I cannot use column header e.g. A:A as reference because they are A:A column and B:B column.

  • @elphau
    @elphau 11 лет назад

    sounds like a solution to my current problem. THANK YOU!

  • @gexcel
    @gexcel 11 лет назад

    Awesome, like always, i like particularly Ctr ', but pay attention mike, if the table are in another sheet, the normal references will not be updated in the summarize table, we have to use the nomenclature.

  • @mysticrosemale
    @mysticrosemale 8 лет назад +2

    Hey, i could not understand the changing of column into dog column. y it is changing. pls tell me.

  • @azusapink
    @azusapink 6 лет назад

    thank you. you always solve my problems

  • @nagarajuv8631
    @nagarajuv8631 3 года назад

    Awesome ✨ 🌟 👍 sir, thanks a lot

    • @excelisfun
      @excelisfun  3 года назад

      You are welcome a lot : )

  • @Braniac_ruddra
    @Braniac_ruddra 6 лет назад

    Thanks alot buddy!!! I have been looking for this for a very long time. God Bless You!!! Keep up this great woooooorrrrrrrrkkkkkkkkkk

    • @excelisfun
      @excelisfun  6 лет назад

      You are welcome!, Amitabh!! Thanks for your support : )

    • @Braniac_ruddra
      @Braniac_ruddra 6 лет назад

      ExcelIsFun you are most welcome buddy!!!

  • @TSquared2001
    @TSquared2001 6 лет назад

    Can you do it with separate sheets and workbooks?

  • @anthonyverdin6743
    @anthonyverdin6743 5 лет назад

    Thanks for the help.

    • @excelisfun
      @excelisfun  5 лет назад

      You are welcome, Anthony!!!!

  • @jay55patel
    @jay55patel 8 лет назад +1

    this video really help me lot thank you very much

  • @TheEfeth
    @TheEfeth 4 года назад

    Brother help me how to use sumifs formula for triple criteria.

  • @SystemOverload69
    @SystemOverload69 9 лет назад

    Does anyone know how to get it so you can copy a formula across the table and allow it to use the column header as the column from the data table to sum.

  • @nepalpankaj
    @nepalpankaj 11 лет назад

    Hi Mike.. You can avoid the table reference from chaning when you copy the formula by copying and pasting instead of dragging the formula to populate other cells with formula

  • @VinayShankam
    @VinayShankam 11 лет назад

    Thanks. I did post to Mr Excel forum and here is the solution/fix as FYI....operation on functions dont need to be in double quotes in SUMIFS "="&TODAY()-WEEKDAY(TODAY(),15)+1)

  • @Icefritz1
    @Icefritz1 9 лет назад

    Would it be possible to insert IF formula in Criteria for with in the SumIfs formula? To calculate for example month in an date Criteria with left or right formula...

  • @vorlandergoal
    @vorlandergoal 11 лет назад

    I'm not sure I get this remark. If the sheet is added to the normal reference as usual, it will still update as long as it is a ctrl+T table.
    However, I always suggest to name the ranges manually to make it easier to enter when typing the formula (the named range will expand)

  • @leemcmindes1464
    @leemcmindes1464 7 лет назад

    how can i use the profit and loss template with downloaded bank statements to make a report of expense categories per month?

  • @ishaq79
    @ishaq79 9 лет назад +1

    Hi,
    My question is related to date, what i mean i want to calculate sumifs with a criteria of size, however i want to calculate particular month for several dates of that particular.
    Could you help me please

    • @excelisfun
      @excelisfun  9 лет назад

      +Abu Rayan Here is a video:
      Excel Magic Trick 1065: SUMIFS: Month Over Month Sales Calculations From Transactional Data Set
      ruclips.net/video/6p6wzUx6H1c/видео.html

  • @MrPouHan
    @MrPouHan 7 лет назад

    SUMIF result shows correct value when I click Insert Function button. But it does not show up in the cell itself. Any suggestions? Thanks.

  • @itsdaj
    @itsdaj 11 лет назад

    Hi Mike,
    Thanks for the help.
    I want to list 6 calculated rows off of my main table one for each of the last 6 months. One of the sumif criteria is that the date is within the last six months. I am not sure how to do that. The date format is mmmyy.
    I am using a cell for the formula to use to refer to the most recent month. How can I use that cell and using sumif to pull months from the most recent minus 6.
    Thank you for any help you can give.

  • @bhumikapatel247
    @bhumikapatel247 6 лет назад

    Thank you, very helpful for budgeting spreadsheet.

    • @excelisfun
      @excelisfun  6 лет назад

      You are welcome, Bhumika! Thank you for the support of the channel with your comment, Thumb Up and Sub : )

  • @chuakianhuat6564
    @chuakianhuat6564 10 лет назад

    May u Share the video how to solve the problem as my schedule below ?

  • @ExcelStrategy
    @ExcelStrategy 11 лет назад

    Another possible strategy is to apply the table format to the source range after that we have created the formulas that points to the source range ! Ps. I can't find EMT n 1023 !!!

  • @zeeshankhawaja2060
    @zeeshankhawaja2060 8 лет назад +1

    Nice Video BUT a tip for you:
    you can lock the reference by typing "=SUM(Table1[[Date]:[Date]]" in column C and now you can copy it and paste it in Column D or F, it shall still refer to Table1[Date]
    Have fun.

    • @smartnc3scyoutubeca68
      @smartnc3scyoutubeca68 7 лет назад

      I was gonna say that! I learned that last year, before I always wondered if there was a way.

  • @zenyhuelgas8128
    @zenyhuelgas8128 7 лет назад

    Thank you!

  • @edwinacuna2577
    @edwinacuna2577 4 года назад

    Hi great material! One question !! How do i sum a column descending by groups. I use sumif but i get a cumulative sum by groups ascending.

  • @nicholeshoong3668
    @nicholeshoong3668 6 лет назад

    YOU ARE THE ONE

  • @anil11996
    @anil11996 11 лет назад

    your excel magic trick 1023 is missing

  • @KristaNCBarril
    @KristaNCBarril 7 лет назад

    Great videos! I have a question. How would you add values with multiple criteria in the same column. Say I wanted to use SUMIFS to find Mandy's amount for Multiple values in the Category without entering, *=sumifs(amount, Dog, "Mandy", Category, "food")+(sumifs(amount, Dog, "Mandy", Category, "accessories")* to generate the amount and instead generate a formula something like *=sumifs(amount, Dog, "Mandy", Category, "food&accessories")* . Sooo I want Mandy's amount with Food and boarding. How would you generate this function?
    Thank you!

    • @excelisfun
      @excelisfun  7 лет назад

      A formula like this will work: =SUMPRODUCT(SUMIFS(Table1[Amount],Table1[Dog],"Fido",Table1[Category],{"Food";"Accessories"}))
      I have made a video for your question, but I can't post it until tomorrow or the next day. Be sure to Sub so you get notified when it is published. And of course, Thumbs Up and comment : )

    • @excelisfun
      @excelisfun  7 лет назад

      Here you go, Krista Noelle Barril, I made this video to answer your question: Excel Magic Trick 1459: Adding Food or Accessory Costs For Each Dog with SUMIFS & SUMPRODUCT, ruclips.net/video/ya7thmoKdO4/видео.html

    • @KristaNCBarril
      @KristaNCBarril 7 лет назад

      ExcelIsFun thank you so much!! This worked! Now just to figure out the null part of it :)

    • @excelisfun
      @excelisfun  7 лет назад

      what do you mean by null?

  • @michalroesler
    @michalroesler 4 года назад

    cool video. very helpful. Watching those videos is like watching Lord of the Rings - they never end :-)

    • @excelisfun
      @excelisfun  4 года назад +1

      Yes, they never end. But that is the beauty of Excel

  • @Eugenesng91
    @Eugenesng91 11 лет назад

    Hi your video is amazing good :) I am a amatuer user of micrsoft products. Is it possible to check with you something regarding my database? Is there any way to contact you?

  • @angieheller182
    @angieheller182 8 лет назад

    Hi there,
    I have an idea which I'm not sure I'd even possible. Basically I have a cell which I want to divide by another cell to get it to a certain percentage eg G6*1.25%
    My question is, can you do a formula and a sumifs? Basically what I want to know is this.
    Can G6*1.25% only if it equals multiple criteria ranges? eg G6*1.25% if B6 equals ="Example 1" & ="Example 2".
    Happy to screenshot and email you to explain further.
    Thanks

  • @vorlandergoal
    @vorlandergoal 11 лет назад

    + named ranges will also fix the copy paste issue

  • @tanishqjain3454
    @tanishqjain3454 8 лет назад

    u r just awesome. need ur autograph

  • @DerickPitcher
    @DerickPitcher 6 лет назад

    Great video!!!! I used this information in another situation but it didn't work. I have 12 sheets (January through December) all have identical layout and another sheet (TD4) to show specific collective data from January through December.
    Employee's names are in range January:December!$C$9:$C$109 and shown either 4 or 5 times. The data I'm looking for is in range January:December!$J$9:$J$109. The criteria is Employee's name located at TD4!$C$2.
    The formula I used that didn't work was:
    =SUMPRODUCT(SUMIF(January:December!$C$9:$C$109,$C2,January:December!$J$9:$J$109))
    The solution I found was that I had to create a named range called "Sheets" with all months (worksheet names) and incorporate the INDIRECT function for it to work:
    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!$C$9:$C$109"),$C2,INDIRECT("'"&Sheets&"'!$J$9:$J$109")))
    Why would "January:December!$C$9:$C$109" not work and why would INDIRECT("'"&Sheets&"'!$C$9:$C$109") work?

  • @mokasrinivas9685
    @mokasrinivas9685 4 года назад

    Hi , I learned maximum tricks from ur page,thanks for that, I need a help from our excel expert's... Any one will help me in A:A column I want to find 2 values which are in a sequel , if I want to find 7,3 , (ex: 10A=7,11A=3)which cells the sequence coming that should show as output to me,

  • @Jda1101
    @Jda1101 10 лет назад

    Sounds just like David Schwimmer!

  • @excelisfun
    @excelisfun  11 лет назад

    I don't know, Try:
    mrexcel [dot] com/forum

  • @MuhammadSajidd
    @MuhammadSajidd 8 лет назад

    nice

  • @waylazy3413
    @waylazy3413 7 лет назад +1

    Love all your video's and the simple and easy to follow instructions. So I followed your instructions in trick1024 and used the function wizard to make sure it was reading the data correctly and according to the line by line of the wizard, it is correct but it says there is a problem with it working and it won't let me save and it doesn't give me a result.
    =SUMIFS(_DataRecords!$F$2:$F$11,Year(_DataRecords!$A$2:$A$11),Year(D$2),_DataRecords!$C$2:$C$11,AnnLaneLot421_EaufalaAL!$A$1,_DataRecords!$E$2:$E$11,AnnLaneLot421_EaufalaAL!$B3)
    I suspect that the problem is that my table is on a different sheet than my formula or maybe its because I have a group of sheets that I'm trying to enter the formula on at the same time.
    Any help would be appreciated :)

    • @waylazy3413
      @waylazy3413 7 лет назад +1

      I figured it out....first of all I forgot to mention that I was using Excel 2007. As to the problem it turns out that my column header 2014 reads as 1/1/2014 but when I used the Year function to get the year from my Date Column in my Data table, the underlying value was ex: 4/12/2014. Since the two dates have different serial values it did not match the Years like I wanted it to. I had to create a hidden column in my data table that took the value in the Date Column and converted it to the first of the year for the stated year in the date. Since the values that are written to my Data Table are from a user form, I then had to figure out how to write that formula in VBA in order to get it assigned to my new column labeled Year Value which is of course hidden. That gave me fits as the video's I looked up said that you could use Date, DateSerial, Year functions by just typing them in. That didn't work. For Excel 2007 : this is what I had to write : oNewRw.range.Cells(1, 2).value = DateTime.DateSerial(DateTime.Year(DTPicker1), 1, 1). This gives me the first day of the first month of any given Year from the Date Picker Value. Now my Sum Ifs formula is able to match the years and sum only those values. I could not have figured this out if it wasn't for all the instructional video's both on Excel and Excel VBA. Thank you. Hope this helps someone else.

  • @tanishqjain3454
    @tanishqjain3454 8 лет назад

    :)

  • @syedtahirbukhari8377
    @syedtahirbukhari8377 2 месяца назад

    Let's watch history lessons

  • @excelisfun
    @excelisfun  11 лет назад +2

    Nice tip! EMT 1023 will be out in one or two days.