Mike, thanks so much for posting these vids almost daily. Your videos have become almost a daily routine for me to watch and practice through. Love that you include workbooks, annotations, shortcuts, etc. Respect.
Super summary of how to use the two formulas, Mike! For those who still want to use the Table nomenclature but don’t want to do all the awkward steps to lock the columns in the formulas, you can use a trick. As Mike stated (5:20), drag-copying H29 to the right would shift the columns. Instead, in H29 create the formula using Table nomenclature, copy the cell, paste into I29, J29 etc. and the columns with Table nomenclature will not shift (but the cell unlocked references will, as desired). Then, double click to send it down.
Glad you appreciate all the small details that go into producing one of these videos. I appreciate the support with your comment, Thumbs Up and Sub : )
Well explained Mike. You reduced the complexity. One suggestion. In the table you could use: names, create from selection. Those names are also dynamic if the table expand. The formulas will be more "readable". One thing: you record all your videos once. Amazing that you do not get nervous at the "mistake" at the end! Respect.
Thanks for the kind words and hot tip : ) But... Record videos once!?!?! What do you mean? That video was one of the easier ones, but it did take four different videos all edited together with at least 1000 edits...
Well, then compliments to your editing skills, it realy looks like you record those 27 minutes with one breath...:) Maybe you make some video about how to make a good screencast. But on the other side.. a good magican does not tell all his tricks to the audience...;)
I just tried to use the whole formula using names. for the ranges in the Excel Table this works. Just select one cell, press ctrl-* and then "create from selection". You can use these names. You can also use a name for H27 (name House) but then you can only copy down, not to the right (makes sense). But then G28..G59. you can give this a name and use this in the formula (attention select date (label) and not DATE (formula). But this gave me a NA#. Even if I reduced the range with the last empty cells. But that's strange. You can define a range with a name, and if you are on the same row, you can refer to every individual cell using the name for the whole range! Any ideas why?
Great one mike ... When will be the next DAX video out ??? please make one complex DAX video.. like Return customers / New customers in DAX patterns ...Please ... your explanations make lot of things clear..
I probably will not do DAX until late March of next year. I am under contract by Highline to create about 100 videos for classes at Highline. But them in March I will be creating a full Power Query DAX Power BI class...
Dear Mike, Thanks for all the hard works, may i pose a question ? Just wondering how would i do this if i want to multiply a selection of range & multiply by another cell (an absolute sell reference) , thanks,
Mike you are an expert... Can you tell me how can i become an expert like you? I want to prepare for Microsoft office specialist exam.. What should i do to prepare for it... Please tell me it would be a great help
Sir i have doubt I have small shop i created invoice bill sheet and pruchase sheet and sales sheet I want my sales sheet to get auto update form invoice billing sheet. Each time i put a bill for my goods i want how many sales happen per day for each goods it should update automatically in sales sheet while i enter the each goods quantity in invoice billing sheet Please slove it
Hello. Mike has tons of videos that would help you in your shop. Try searching for "ExcelIsFun SUMIFS Sales" or something in the RUclips. I just did that and got this video of Mike's. Hope it helps.: ruclips.net/video/SQUbL02XvGw/видео.html
Great video as always !! Thanks Mike :-) Try to change the last argument of COUNTIFS in your last example (cell V29) to {"=",">="}&$T29:$U29 and see what happened ;-))))
Mike, can you tell me why my formula isn't working? The only difference I did was instead of adding the two arrays together like you did in one part of the SUMPRODUCT, I separated them. =SUMPRODUCT(--($C$27:$C$10600=$G$29),--($E$27:$E$10600=H$28))
Yes, you have all AND Conditions, there was one OR condition in the formula I created. The plus symbol in the formula in the video is for an OR Logical test when there is no chance of double counting.
Oh! I didn't understand it worked like that! One more ?: Couldn't you separate the array arguments with the multiplication "*" sign instead of commas and get the same result? Thank you so much!
I discussed OR Criteria in the video!!! I also gave you a link for more study about OR Logical Tests. Yes, you can use *, but in the video I mentioned that double negative is usually the most efficient methods.
Mike, thanks so much for posting these vids almost daily. Your videos have become almost a daily routine for me to watch and practice through. Love that you include workbooks, annotations, shortcuts, etc. Respect.
Glad the videos help. Thanks for the respect! I appreciate your continued support with your comments and Thumbs Up : )
Super summary of how to use the two formulas, Mike! For those who still want to use the Table nomenclature but don’t want to do all the awkward steps to lock the columns in the formulas, you can use a trick. As Mike stated (5:20), drag-copying H29 to the right would shift the columns. Instead, in H29 create the formula using Table nomenclature, copy the cell, paste into I29, J29 etc. and the columns with Table nomenclature will not shift (but the cell unlocked references will, as desired). Then, double click to send it down.
Nice!!!!! Thanks for the tip : )
Love the annotations in the description. Skipping the introductions to the right spot rocks. Keep up the good work
Glad you appreciate all the small details that go into producing one of these videos. I appreciate the support with your comment, Thumbs Up and Sub : )
Another excellent comparison vid, my vote for COUNTIFS!
Thanks for the vote : )
Which one? The first, second, or Bill Szysz's suggestion?
Thank you Mike for another EXCELlent video
You are welcome! Thank you for your EXCELlent support : )
Awesome video Mike!
Glad you like it!! Thanks for your support with your comment, Thumbs Up and Sub : )
Great! Thumbs up!
Thanks for the Thumbs Up and Comment : )
Well explained Mike. You reduced the complexity.
One suggestion. In the table you could use: names, create from selection. Those names are also dynamic if the table expand. The formulas will be more "readable".
One thing: you record all your videos once. Amazing that you do not get nervous at the "mistake" at the end! Respect.
Thanks for the kind words and hot tip : ) But... Record videos once!?!?! What do you mean? That video was one of the easier ones, but it did take four different videos all edited together with at least 1000 edits...
Well, then compliments to your editing skills, it realy looks like you record those 27 minutes with one breath...:) Maybe you make some video about how to make a good screencast. But on the other side.. a good magican does not tell all his tricks to the audience...;)
I just tried to use the whole formula using names. for the ranges in the Excel Table this works. Just select one cell, press ctrl-* and then "create from selection". You can use these names. You can also use a name for H27 (name House) but then you can only copy down, not to the right (makes sense). But then G28..G59. you can give this a name and use this in the formula (attention select date (label) and not DATE (formula). But this gave me a NA#. Even if I reduced the range with the last empty cells. But that's strange. You can define a range with a name, and if you are on the same row, you can refer to every individual cell using the name for the whole range! Any ideas why?
Awsome Mike great video
Glad you like it.
Great one mike ...
When will be the next DAX video out ??? please make one complex DAX video.. like Return customers / New customers in DAX patterns ...Please ... your explanations make lot of things clear..
I probably will not do DAX until late March of next year. I am under contract by Highline to create about 100 videos for classes at Highline. But them in March I will be creating a full Power Query DAX Power BI class...
ExcelIsFun ..... Ohhhhhhhhhh 😢😢
Great video
Glad you like it!
Nice One Mike. Thanks :)
You are welcome! I thought of your love of formula videos when I posted this one : )
I will always appreciate if you keep me in mind so you will keep making videos like this. :) :)
Cool!!!
Dear Mike, Thanks for all the hard works, may i pose a question ?
Just wondering how would i do this if i want to multiply a selection of range & multiply by another cell (an absolute sell reference) , thanks,
Mike you are an expert... Can you tell me how can i become an expert like you?
I want to prepare for Microsoft office specialist exam..
What should i do to prepare for it...
Please tell me it would be a great help
4:50 Options > Use table names in formulas
Sir i have doubt
I have small shop i created invoice bill sheet and pruchase sheet and sales sheet
I want my sales sheet to get auto update form invoice billing sheet.
Each time i put a bill for my goods i want how many sales happen per day for each goods it should update automatically in sales sheet while i enter the each goods quantity in invoice billing sheet
Please slove it
Hello. Mike has tons of videos that would help you in your shop. Try searching for "ExcelIsFun SUMIFS Sales" or something in the RUclips. I just did that and got this video of Mike's. Hope it helps.: ruclips.net/video/SQUbL02XvGw/видео.html
Great video as always !! Thanks Mike :-)
Try to change the last argument of COUNTIFS in your last example (cell V29) to
{"=",">="}&$T29:$U29 and see what happened ;-))))
Very Clever!!! No need for the helper cells! Thanks O Great Bill Szysz : ) : ) : )
I would go with the last method and using Bill's last clever construction w/o helper columns.
Yes! I agree : )
The link to the video refers to EMT1462 and not 1467
I will have to fix that when i get to work in about an hour... Sorry about that!
Mike, can you tell me why my formula isn't working? The only difference I did was instead of adding the two arrays together like you did in one part of the SUMPRODUCT, I separated them.
=SUMPRODUCT(--($C$27:$C$10600=$G$29),--($E$27:$E$10600=H$28))
Yes, you have all AND Conditions, there was one OR condition in the formula I created. The plus symbol in the formula in the video is for an OR Logical test when there is no chance of double counting.
Oh! I didn't understand it worked like that! One more ?: Couldn't you separate the array arguments with the multiplication "*" sign instead of commas and get the same result? Thank you so much!
I discussed OR Criteria in the video!!! I also gave you a link for more study about OR Logical Tests. Yes, you can use *, but in the video I mentioned that double negative is usually the most efficient methods.
Thank you for the response. Sorry for making my Teacher upset. jk haha I will rewatch the video and check out those other links.
: )
why doesnt this work? =SUMPRODUCT((TB!A:A="IS-1*")*(Months
Months is a named range contains months