Excel Formula for Lookup Counting with COUNTIFS & SUM- Excel Magic Trick 1603

[@davebowman5392]

Thanks Mike

[@excelisfun]

You are welcome, Dave!

[@excelisfun]

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... : (

[@sumithreddy196]

But your videos are informative .. thanks and keep it up

[@excelisfun]

@@sumithreddy196 Okay : ) Glad they help you, mumith!

[@mohamedchakroun4973]

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

[@excelisfun]

@@mohamedchakroun4973 , Thanks for helping the Team!!!!! Ya, bad data... : (

[@excelisfun]

@@mohamedchakroun4973 Thanks for helping out the Team : ) : )

[@simfinso858]

Wow Nice Counting

[@excelisfun]

Glad you like it, Phone Excel Time : )

[@GeertDelmulle]

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!

[@excelisfun]

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 ; )

[@richardhay645]

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!

[@excelisfun]

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

[@LeilaGharani]

Great! Thank you Mike for a great formula combo.

[@excelisfun]

You are welcome, Teammate Leila!!

[@davegoodo3603]

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!

[@excelisfun]

You are welcome for the videos and files, Dave!

[@SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Teammate Syed : )

[@wayneedmondson1065]

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!

[@excelisfun]

You are welcome, Wayne! Thanks for your kind comments : )

[@michaelmckeithen6220]

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!

[@pmsocho]

Great trick!

[@excelisfun]

Thanks, Teammate pmsocho!!!!

[@DougHExcel]

thanks for the lookup adding trick!

[@excelisfun]

Lookup counting. You are welcome, Doug : )

[@excelisfun]

BTW, Doug, At 02:54, I mistakenly said: "Lookup Adding" rather than "Lookup Counting".

[@ogwalfrancis]

Great trick, You are really a great fun of sumproduct, Thanks You so much

[@excelisfun]

You are welcome so much, Ogwal!

[@MalinaC]

Always up-to-date, even though there is the new calculation engine. Thanks, Mike!

[@excelisfun]

You are welcome Teammate Malina!!!!

[@chrism9037]

This was great thanks Mike!!!

[@excelisfun]

Glad it was great, Teammate Chris!

[@bassisessaidexcel]

Merci

[@excelisfun]

You are welcome!

[@mohamedchakroun4973]

Thanks for this review...Bonus thumbs Up

[@excelisfun]

Yes, review for sure. You are welcome for the bonus review : )

[@tulsidasjamnani9455]

Hi... Mike sir , This is also working in Google sheets .... =ARRAYFORMULA(SUM(COUNTIFS(A2:A,C2:C))) As the same...

[@excelisfun]

Cool, thanks for the hot tip, Tulsidas : )

[@tulsidasjamnani9455]

@@excelisfun Thanks for reply..

[@dereklowry3111]

excellent

[@excelisfun]

EXCELlent ; )

[@babarqureshi5043]

Thanks

[@excelisfun]

You are welcome, Babar!!!

[@Xcwizard]

👍👍 Greate Viedo Mike. I have another way to count =COUNT(MATCH(A2:A1402,C2:C87,)) or =COUNT(XMATCH(A2:A1402,C2:C87))

[@excelisfun]

I love it, bo!!!!! That is very comparable!!!!

[@hashi856]

Just keep teasing us with those Dynamic arrays. #ForeverInBeta

[@excelisfun]

But when the come, it will be amazing ; )

[@ubaidillahmuhammad20]

nice job. please give me permission for other file sir...

[@excelisfun]

The servers are back up!!!!