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"
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.
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.
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.
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.
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
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)
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...
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)
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
+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
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.
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 !!!
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.
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!
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 : )
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
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?
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
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?
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,
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 :)
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.
Just a guy having fun with Excel!
I am glad that you like the video!
That Table feature is annoying...
Glad you like the video!
Thanks for your support in buying the book!
Great tip about formulas across sheets!!
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"
+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!!!
Thanks for watching, krn14242!
You are welcome!
That is a hot tip! I will make a video to pass along your hot tip! Thanks!
Nice tip! EMT 1023 will be out in one or two days.
i never knew that the absolute cell references would be dynamic if you're referring to a table.
THIS VIDEO CHANGED MY LIFE.
+lazyorbit86 Glad you liked the video!!! :)
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.
Awesome!
Thank you so much !!!!! You have shared all data for studying, you are so generous !!!!
I am big fan of you.
Cool trick. Never thought of selected range -1 to avoid the table nomenclature. Thanks Mike
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
That is awesome!!! You are playing it forward : ) : )
We continue learning, thank you very much for your teachings
You are magician of excel
Thank you very much.... After so many videos I found what I want.
You are welcome!
For back and forth dialog to get Excel solutions try THE best Excel question site:
mrexcel [dot] com/forum
This is exactly what i am looking for. Tq for making my job more effective.
You are welcome for the More Effective!!! Thanks for the support, Roziana, with your comments, Thumbs Ups and Sub : )
I love this channel.
Knowledge is just overwhelmingly good!
+TheMesoPeaks Glad the knowledge helps!
Yep...I need all of them at work : )
Great Video....... clears every thing.
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.
sounds like a solution to my current problem. THANK YOU!
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.
Hey, i could not understand the changing of column into dog column. y it is changing. pls tell me.
thank you. you always solve my problems
Awesome ✨ 🌟 👍 sir, thanks a lot
You are welcome a lot : )
Thanks alot buddy!!! I have been looking for this for a very long time. God Bless You!!! Keep up this great woooooorrrrrrrrkkkkkkkkkk
You are welcome!, Amitabh!! Thanks for your support : )
ExcelIsFun you are most welcome buddy!!!
Can you do it with separate sheets and workbooks?
Thanks for the help.
You are welcome, Anthony!!!!
this video really help me lot thank you very much
Brother help me how to use sumifs formula for triple criteria.
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.
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
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)
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...
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)
how can i use the profit and loss template with downloaded bank statements to make a report of expense categories per month?
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
+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
SUMIF result shows correct value when I click Insert Function button. But it does not show up in the cell itself. Any suggestions? Thanks.
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.
Thank you, very helpful for budgeting spreadsheet.
You are welcome, Bhumika! Thank you for the support of the channel with your comment, Thumb Up and Sub : )
May u Share the video how to solve the problem as my schedule below ?
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 !!!
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.
I was gonna say that! I learned that last year, before I always wondered if there was a way.
Thank you!
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.
YOU ARE THE ONE
your excel magic trick 1023 is missing
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!
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 : )
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
ExcelIsFun thank you so much!! This worked! Now just to figure out the null part of it :)
what do you mean by null?
cool video. very helpful. Watching those videos is like watching Lord of the Rings - they never end :-)
Yes, they never end. But that is the beauty of Excel
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?
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
+ named ranges will also fix the copy paste issue
u r just awesome. need ur autograph
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?
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,
Sounds just like David Schwimmer!
I don't know, Try:
mrexcel [dot] com/forum
nice
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 :)
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.
:)
Let's watch history lessons
Nice tip! EMT 1023 will be out in one or two days.