Good for you that you keep enforcing the concept of array calculations, Mike. Not too many people use them and they can make life way easier in many situations. I don’t mind using SUMPRODUCT() for now, we’re used to it. This one is for you MS:... (:-p) LOL!
I was going to mention xlookup but looks like you're already making that video!!! :) I don't envy you having to teach both old and new calc engines maybe indefinitely!
Well... I will problem have to teach both for many years to come... When AGGREGATE came out in 2010, it look at least until 2016 until many people could use it...
Thanks for such a creative application of COUNTIFS, SUM and SUMPRODUCT. Your videos and accompanying files are a great resource for me and I have learnt so much from them. Thanks so much Mike! Keep up your great work!
Hi Mike.. great video and explanation of delivering the entire helper column into an array in a single cell. Once that concept is understood, it unlocks a whole new world of calculation and manipulation possibilities. Thanks for sharing all of your insider tips, tricks and techniques to get work done faster and more efficiently. Thumbs up!
I made two errors in this video: 1) There are two duplicate King County zip codes: 98092 is a dup in the range C48:C49 and 98102 is a dup in the range C51:C52. They should not be there. If you delete them, the correct Count total is 1,096. 2) At 02:54 I said "Lookup Adding" rather than "Lookup Counting". Sorry about these mistakes... : (
1) There are Three Dup Mike, you missed the one in C14:C15 , but it has no effect in the count total it remain 1,096. 2) In A1143 may be an extra 8 was added to the zip code at the end 98019(8) instead of 98019, if it is the count total will be 1,097
Great video sir, it's what I was looking for! However, I can't get mine to count the actual number that in the cell. No matter what number I place in my cell, I only get +1 instead of +3, or +4, etc, etc. Please tell me what I'm doing wrong someone? Thank you in advance!
Good for you that you keep enforcing the concept of array calculations, Mike. Not too many people use them and they can make life way easier in many situations.
I don’t mind using SUMPRODUCT() for now, we’re used to it.
This one is for you MS:... (:-p)
LOL!
Yes, it REALLY can help if you understand Array Formulas. But as you say, not many do... Yes, I have no problem using SUMP-Tab either ; )
I was going to mention xlookup but looks like you're already making that video!!! :) I don't envy you having to teach both old and new calc engines maybe indefinitely!
Well... I will problem have to teach both for many years to come... When AGGREGATE came out in 2010, it look at least until 2016 until many people could use it...
Great! Thank you Mike for a great formula combo.
You are welcome, Teammate Leila!!
Wow Nice Counting
Glad you like it, Phone Excel Time : )
Thanks Mike
You are welcome, Dave!
Thanks Mike for this EXCELlent video.
You are welcome, Teammate Syed : )
Thanks for such a creative application of COUNTIFS, SUM and SUMPRODUCT. Your videos and accompanying files are a great resource for me and I have learnt so much from them. Thanks so much Mike! Keep up your great work!
You are welcome for the videos and files, Dave!
Hi Mike.. great video and explanation of delivering the entire helper column into an array in a single cell. Once that concept is understood, it unlocks a whole new world of calculation and manipulation possibilities. Thanks for sharing all of your insider tips, tricks and techniques to get work done faster and more efficiently. Thumbs up!
You are welcome, Wayne! Thanks for your kind comments : )
I made two errors in this video:
1) There are two duplicate King County zip codes: 98092 is a dup in the range C48:C49 and 98102 is a dup in the range C51:C52. They should not be there. If you delete them, the correct Count total is 1,096.
2) At 02:54 I said "Lookup Adding" rather than "Lookup Counting".
Sorry about these mistakes... : (
But your videos are informative .. thanks and keep it up
@@sumithreddy196 Okay : ) Glad they help you, mumith!
1) There are Three Dup Mike, you missed the one in C14:C15 , but it has no effect in the count total it remain 1,096.
2) In A1143 may be an extra 8 was added to the zip code at the end 98019(8) instead of 98019, if it is the count total will be 1,097
@@mohamedchakroun4973 , Thanks for helping the Team!!!!! Ya, bad data... : (
@@mohamedchakroun4973 Thanks for helping out the Team : ) : )
thanks for the lookup adding trick!
Lookup counting. You are welcome, Doug : )
BTW, Doug, At 02:54, I mistakenly said: "Lookup Adding" rather than "Lookup Counting".
Great trick!
Thanks, Teammate pmsocho!!!!
Always up-to-date, even though there is the new calculation engine. Thanks, Mike!
You are welcome Teammate Malina!!!!
Great trick, You are really a great fun of sumproduct, Thanks You so much
You are welcome so much, Ogwal!
Thanks for this review...Bonus thumbs Up
Yes, review for sure. You are welcome for the bonus review : )
Great video sir, it's what I was looking for! However, I can't get mine to count the actual number that in the cell. No matter what number I place in my cell, I only get +1 instead of +3, or +4, etc, etc. Please tell me what I'm doing wrong someone? Thank you in advance!
This was great thanks Mike!!!
Glad it was great, Teammate Chris!
Merci
You are welcome!
Hi...
Mike sir ,
This is also working in Google sheets ....
=ARRAYFORMULA(SUM(COUNTIFS(A2:A,C2:C)))
As the same...
Cool, thanks for the hot tip, Tulsidas : )
@@excelisfun Thanks for reply..
👍👍 Greate Viedo Mike.
I have another way to count
=COUNT(MATCH(A2:A1402,C2:C87,))
or
=COUNT(XMATCH(A2:A1402,C2:C87))
I love it, bo!!!!! That is very comparable!!!!
excellent
EXCELlent ; )
Thanks
You are welcome, Babar!!!
Just keep teasing us with those Dynamic arrays. #ForeverInBeta
But when the come, it will be amazing ; )
nice job. please give me permission for other file sir...
The servers are back up!!!!