Unique Count in Excel | How to count Unique Values in a list using Sumproduct and CountIF Formula

Рет қаралды 68,365

3 жыл бұрын

Learn how to count Unique Values In Excel with sumproduct and countif formula. or Count the number of unique values in excel.If we have a list of duplicate values in a range we want to count only the unique occurrences of each value.
Kindly note that excel doesn’t have any single formula that can directly help us to count unique values in excel inside a range.
Watch this video to learn in depth explanation how to count unique.
Download the Assignment Master Practice Folder here (All Playlist Example at one place)
drive.google.com/drive/folder...
(Playlist Folder-Excel Tips and Tricks-UniqueCount.xlsx)
Website - "www.innozant.com"
Let's Connect:
Instagram ► / innozantoffi. .
Facebook ► / innozant
Twitter ► / innozant
Linkedin ► / inno. .
#excelinterviewquestions #misinterviewquestionsandanswers #Innozant #uniquecountexcel #uniquecount

Пікірлер: 240

@rajankumarmishra4057 7 ай бұрын

Hi Dilip Sir, I am glad to inform you that your video is too effective and simple to understand its logic of functions and formulas. I don't have word to express myself about your youtube tutorials. i have learnt a lot in excel and on daily basis, i try to learn and practice more. Thank you so much to prepare such kind of important and useful videos who wants to learn through youtube channel. I also inform you that in my initial time, i had subscribed your channel.

@Innozant 7 ай бұрын

Thanks Rajan for your valuable appreciation.Really means a lof to me.Keep Learning & Keep Exploring👍

@Herukamahesh Жыл бұрын

...aapka dil bohot bada hai bhai....free main itne knowledge sab ko dete ho....🙏...God bless....!!! You are man of integrity...!!!

@Innozant Жыл бұрын

Thanks dear...Keep Learning ❤️

@suvadipnarayanchowdhury4614 3 жыл бұрын

I have seen so many tutorials in KZfaq, but your way of explaining the logic and why it is happening is just WOW...Lots and lots of applause to you for making such great and true learning videos. You are truly doing good to the overall IT Industry and social well being. Social well being because, positivity comes from knowledge and you are sharing your knowledge which is being converted into positivity. Thanks a lot...Please continue this good work...

@Innozant 3 жыл бұрын

M speechless..Thanks Chowdhury sahab..Hope these video tutorials will adds some values in your life..Thanks for being part of Innozant family .

@thetourminator 2 жыл бұрын

Holy krap ! Just got done watching 3 other videos on this topic trying to do what you say at 3:30, but watching those videos (English speakers, and I only understand English) well none of those videos explained how to do what I needed. Just watched this video and despite him speaking a language I can't decipher, I was able to understand how to do what I needed to do in EXCEL (again, 3:30 of video). This dude is awesome....excellent visuals.

@PawanSAINI-ng9fq 2 жыл бұрын

Sir I have. I word to describe your teaching methods but I'm happy to have you my excel teacher who teach me excel very easy and deeply way which no one can do ❤️🙏sir god bless you 👍

@Innozant 2 жыл бұрын

Thanks Pawan for your valuable appreciation dear.Really means a lot to me

@Aggrey_Nkololo 2 жыл бұрын

YOU ARE BRILLIANT!

@hamzazayd3463 Жыл бұрын

Way of explanation is excellent, thanks for sharing your vedios.

@ranvir0392 3 жыл бұрын

Best method to find count unique values.......best teacher ever.....

@Innozant 3 жыл бұрын

Thanks Ranvir for yout valuable appreciation ❤️❤️🙏🙏

@hemantgusain1260 3 жыл бұрын

Thanks you sir for explain such important topics.

@Innozant 3 жыл бұрын

Thanks Hemant,Happy Learning

@vikranthpatro1872 2 жыл бұрын

Great way of explaining the things. Really nice 👌👌👌👌👌

@Innozant 2 жыл бұрын

Thanks Vikrant Ji.Happy learning

@ArmanKhan-iq3ji 3 жыл бұрын

Omg thank you for sharing this wonderful video I'm searching this method from past 2 weeks

@Innozant 3 жыл бұрын

Thanks Arman...Happy learning dear..God bless you..Happy learning

@DeepakVerma-vb3uu 2 жыл бұрын

your way of explanation if just amazing sir. if you have any video about extracting unique list from duplicate items with using formula, please share that video with me. i will.be very thankful to you for this ❤️

@PawanSAINI-ng9fq 2 жыл бұрын

Before subscribed your channel sir I saw many of you tube channel but didn't get kind of content what you deliver for your learner how easily and deeply you described concept is fantastic sir 🙏I learnt lot of tricks and basic of excel for last one month which is unbelievable for me as bigners 🙏 sir

@Innozant 2 жыл бұрын

Thanks Pawana ji.Learn ..enhance your skills on regular basis..❤️

@RMKserve 3 жыл бұрын

My thirst for learning going to be increased day by day as I continue to watch your videos. This is quality of really guru🇵🇰🇵🇰❤️♥️

@Innozant 3 жыл бұрын

Thanks Raja Rizwan..Really means a lot to me dear..Happy learning

@satishojha6240 3 жыл бұрын

Sir your excel video mindblowing and encouraged to all excel learners.Good night

@Innozant 3 жыл бұрын

Thanks Satish ,Apke aise comments really mujhe motivate karta hai..acha. Sikhaun aplogon ko..aur aap life me aage badhen

@SLOANchetan 11 ай бұрын

Best indian excel guide!!🔥🔥

@Innozant 11 ай бұрын

Thanks dear

@staterolife1651 3 жыл бұрын

Excellent 👌 sir.. thanks for video.

@Innozant 3 жыл бұрын

Thanks Sandeep,Have a wonderful day. Happy Learning

@alpeshpanchal7994 2 жыл бұрын

Nice video.. i am looking this formula from last 1 week.... Thanks.

@yogeshrao5924 2 ай бұрын

Thank you Dilip sir Maa kali or apke sahyog se mujhe kaafi Kuch shikhne ko mila apne mujhe creative bna diya dhanyavaad gurujii jai maa kali ❤

@survesatishs 3 жыл бұрын

Excellent!!!!

@vikramgupta3827 3 жыл бұрын

Sir, excellent video, aapne bahut easy kar diya

@Innozant 3 жыл бұрын

Thanks Vikram Ji

@vishwadeeptayde5179 3 жыл бұрын

Your way of teaching is quite good... Keep doing good work for learners...🙏

@Innozant 3 жыл бұрын

Thanks Vishwadeep ji..Happy learning

@vivekmusafir4492 3 жыл бұрын

Thank you for your lessons...I have been using subtotal very often... mostly on top of headers...so whenever any particular name of company/person is selected using simple filter..subtotal automatically updates figures right on top...but that doesn't work when I want to see unique count of any particular field associated to that company or person selected through filter....I have applied combination of sumproduct and cuntif when filter isn't applied it gives me correct unique count...but when any particular company etc. Is selected..unique count remains same...because may be the range given in formula is static and not dynamic.. basically I want to see unique count being updated the way total is updated using subtotal... whenever filter is applied.....hope I was able to convey my query..

@shreyabora4144 Жыл бұрын

Very well explained...

@Innozant Жыл бұрын

Thanks Shreya..Keep Learning ..Keep exploring

@nitishjaiswal6216 3 жыл бұрын

Amazing, I tried so many things but failed. This formula worked for me and I got the Solution. Thank You so Much Sir 🙏

@Innozant 3 жыл бұрын

Thanks dear ❤ Nitish for this valuable comment ..God bless you ..Happy learning

@vyankteshmozare8623 3 ай бұрын

Thankyou very much!

@charviethakore651 2 жыл бұрын

really good explanation

@Innozant 2 жыл бұрын

Thanks Charvie.We welcome you to experience the taste of success with Innozant -Seed of success. ❤️❤️❤️

@sandeepaswal8944 3 жыл бұрын

नमस्ते सर जी, सर आपने Sumproduct and CountIF with Unique Values बहुत ही डिटेल मैं और अच्छे से समझाया | मुझे आज नई चीज़ सीखने को मिली | आपका बहुत बहुत धन्यवाद सर जी |

@Innozant 3 жыл бұрын

Thanks Sandeep Ji..Aaj Live Interview of MIS EXECUTIVE video aane wala hai..must watch

@gurunadhnaladi5722 3 жыл бұрын

First of all thank you so much for making myself confident on excel day by day. Total credit goes to you only my dear brother... Pls suggest that how we can use this formula along with V-lookup

@Innozant 3 жыл бұрын

Thanks 🙏❤️

@ankitasahu9645 Жыл бұрын

You are genius sir ,Thank you for this video😊😊

@Innozant Жыл бұрын

Thanks Ankita.. Really means a lot to me dear ❤️👍

@jk-iz7ow 3 жыл бұрын

Sir ji apka vedio or smjane ka trika bhut acha h ase hi vedio bnate rhna

@Innozant 3 жыл бұрын

Thanks dear..Humesha milta rahega videos seekhne ko..

@shrikantbadge3978 3 жыл бұрын

I am moving out of my comfort zone. There is still a lot to learn. Thanks Sir.

@Innozant 3 жыл бұрын

Best of luck my dear friend..Go ahead,Have a wonderful career.

@mahendrapratapsingh9890 3 жыл бұрын

Great concept sir, Thanks, Kindly advice If I want to sum of "Salary" column for only unique occurrence. as this case, 4 kind of different unique occurrence then How to sum of salary column only for this occurrence

@primeknowledge99 2 жыл бұрын

Thanks 👍

@munishwarhajare643 3 жыл бұрын

THANKS SIR!!!!!!

@SarojKumar-dg1su Жыл бұрын

Searching for this type of formula.. Thanks

@Innozant Жыл бұрын

❤️👍

@bishtlokesh1297 3 жыл бұрын

Good sir ji

@sridhardubasi5088 3 жыл бұрын

excellent sir..

@Innozant 3 жыл бұрын

Thanks Sridhar❤🙏

@devokedevtech2750 3 жыл бұрын

Excellent sir

@Innozant 3 жыл бұрын

Thanks dear

@subramanyaba9297 3 ай бұрын

Good teaching

@akak3585 2 жыл бұрын

Thanks for videos sirG

@Innozant 2 жыл бұрын

Thanks dear ❤️

@bhoopendraraghav9781 3 жыл бұрын

Very nice sir.

@Innozant 3 жыл бұрын

Thanks Bhoopi

@gauravbhatia7987 3 жыл бұрын

Thnq sir

@brijeshshah574 10 ай бұрын

So much respect for you Sir. The way you explained is too good.🙏 Sir how can I count unique value based on condition?

@Innozant 10 ай бұрын

Thanks Brijesh ji...Aap series dekhte rhiye bht saare sawal ke jawan apko khud ba khud mil rahe honge

@AhsanUllah-kt1sb 2 жыл бұрын

Brother thanks for details explanation video, really it's awesome video. I have a request and for the solution. Same I need unique count with multiple criteria and one criteria from different columns

@paribaghel1758 2 жыл бұрын

Thnx u sir 🤗

@Innozant 2 жыл бұрын

Thanks Pari, Recommend you to start your learning journey from playlist Basic excel step by step in sequence followed with MIS interview cracker.Have a wonderful career ahead❤️❤️❤️👍

@ranjeetyadavyaduvanshi5155 Жыл бұрын

Nice sir

@aponbahana 3 жыл бұрын

thanks

@Innozant 3 жыл бұрын

Thnsk dear🙏❤️🙂

@ShivamKatheriya-yz6wf Жыл бұрын

Mind-blowing sir I have a question how to count of only capital letter Text in column

@Noumanjavedshamsi 2 жыл бұрын

Intelligent Sir

@Innozant 2 жыл бұрын

Thanks dear❤️

@shrikantbadge3978 Жыл бұрын

Ye series sabse alag hai sir. Baar bar dekhta hu mai

@Innozant Жыл бұрын

Good Shrikant..Aap revision mode on kar rakhe hain aapna..Keep growing

@ankitmehra3074 2 жыл бұрын

sir app gyan ka sagar ho 🥰👌👌👌👌

@Innozant 2 жыл бұрын

Thanks Ankit❤️😊

@vikassinghjayara510 3 жыл бұрын

Hello Sir, Thanks for making the videos and motivate us to learn always new things. As you explained the unique count without helping column, could you please help me to calculate the total count of duplicate value without helping column.

@Innozant 3 жыл бұрын

Ok..in upcoming videos will try to add this question.

@vikassinghjayara510 3 жыл бұрын

@@Innozant Thanks Sir. I will wait for that video.

@AnandaKishoreBanerjee 2 ай бұрын

Respected Dilip Sir, Your video is too good to learn Excel. Sir I have a problem, how can I get unique man power in sum count between time.

@sarcar2370 3 жыл бұрын

Can this be done with countifs

@pravinkharat5088 2 жыл бұрын

Which formula for perticular item or party in different date comes and we have to count how many times that item/party in less than 7 Days and more than 7 Days in Big data.

@anilgami5257 2 жыл бұрын

In same case if one more value like permanent employee or contract employee are use so how can count only contract employee or permanent employee

@PintuKumar-dt9bt 3 жыл бұрын

can you tell me how to list unique name from 2 name list. in this video you have shown how to find unique name count .

@BankerWriterMosharaf 4 ай бұрын

Please make a video on how to replace series of Duplicate Values with Blanks / Zeros while retaining the first occurrence or last occurrence

@uniquemobiletipstricks7714 10 ай бұрын

Dive Deep Thats Innozant

@Innozant 10 ай бұрын

Thanks

@vdeva2766 3 жыл бұрын

Sir. Ek video esi bnau jisme pivot k through unique value count sum kr ske plz..

@pushp38 3 жыл бұрын

That was a great Tip. What if there lots of conditions. "Count Uniques with CountIfs"

@Innozant 3 жыл бұрын

❤️🙏

@rajnish94 Жыл бұрын

Sir, How to count Unique with multiple criteria like employee id wise channel partners...

@nancikalidin8629 Жыл бұрын

If the formula need to be done on a new worksheet..will that work? Am looking for formula to count unique values by country level means a lot of data

@nancikalidin8629 Жыл бұрын

Want to know the count with duplicate and without duplicate values by country level (do we need a specific criteria from each sheet of country?)

@danishrahman7283 15 сағат бұрын

brother counta with unique function does the same thin

@jindagijhand8391 5 ай бұрын

Sir can we find the last nth match value?

@vijaysahal4556 3 жыл бұрын

you are really great sir 👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

@Innozant 3 жыл бұрын

thanks dear❤❤

@ABHISHEKSHARMA-vk9yd 11 ай бұрын

Wow. Me bhi excel me job karta hu but esa logic me kabhi nahi soch skta

@Innozant 11 ай бұрын

👍

@srikantamandal4858 3 жыл бұрын

excel banane walo ko vi itna pata nehi hoga ki excel formula ko itna alag tarikha se cherkhani ki ja sakti hey. sir app jab formula likhte ho mere kheyal formula vi confused ho jati hongi ki karna keya tha or wo kar keya raha hey. hahhahahahhahahah. nice sir . very nice vdo.

@Innozant 3 жыл бұрын

Hahaha..Really means a lot to me..Aapke ye word mere liye kiss gift se kam nhi hai Srikanta.Aaapko humesha kuch better aur alaga seekhane ki koshish hogi meri

@hiteshchourey9730 5 ай бұрын

Macro with pivot table ke saath kese banayege. Video banaye.

@sujitkolhe1298 3 ай бұрын

insted of blank if few cells contain "-", then how to delete count of "-"

@sunnybest3412 2 жыл бұрын

Good morning Sir Thank u sabhi videos k liye Main chahta hu apke is data me se dusri sheet me main employee I'd ek bar dalu to uske samne uski teeno salary aa jaye with formulas

@ramzein776 3 жыл бұрын

very useful video sir, for exa- column A has invoice no , column B has customer ID now I want to count the invoices of each customer ? it can be done using countifs range is customer id as it counts in that column how many Cus id are there as same invoice no, but if in column A some rows remains no invoice no column B row has Cus id the result will b wrong as it counts Cus id but in that specific row has no invoice no (for some reason say payment given for previous invoice) Pls advise how to find it

@Innozant 3 жыл бұрын

Rqmzein Sahab it's better you may share your query excel sheet on @innozantofficial telegram.will See your query and try this make time for the same

@Papanmyprince 10 ай бұрын

Sir, Mere pas aysa hi ekta data he lekin sir usme employee visit many customer in a day some Times employee visit same customer two time in a day, mujhe duplicket valu nikal he using same employee visit same customer using function. Please help

@samadhan251 3 жыл бұрын

Sir can you please make a video on Creating a unique List. (without using unique formula)?

@Innozant 3 жыл бұрын

Unique list question is already there in interview Cracker series.Ok will make seprate video for the same.❤️🙏

@jeganm5502 3 жыл бұрын

Thanks a lot 👍..After watching this video I am able to do it. But, Pls teach in English sir... I am not able to understand the reason y the dividing consept is used.

@Innozant 3 жыл бұрын

Thanks Jegan..

@imranather74 Жыл бұрын

This use with filter kindly help

@jaybharat2374 Жыл бұрын

How count days between two dates of list of items pls

@abhijitdey1819 Жыл бұрын

Sir agar dono coloum me se ya 3 coloum me se unique count nikalna hai to kya formula hoga. Please suggest me

@Innozant Жыл бұрын

Distinct count bataya hua hai detail me maine ..pls check Excel maharaj playlist

@abdulajij7569 Жыл бұрын

Mene kal ek mis ka interview join Kiya or mere se technical Excel ka question pucha gaya ki jisme customar name or customer ID di huyi thi or customar I'd common thi i reviewed ne bola ki unique I'd k aadhar par customer ki margin pickup karni h wo bhi without pivot table k please reply

@chetan1008 2 жыл бұрын

👍👍👍

@Innozant 2 жыл бұрын

❤️

@hirendrashriwas8021 Жыл бұрын

Sir दो batch example (batch 1 and batch 2 ) ki same date na ho vertical me ye kese kare...I am facing big problem pls help me sir, how can I show data to you sir.

@vishalbhati912 Жыл бұрын

Bahot bahatreen. Sabhi formulas ka aapne postmortem kar rakha he

@Innozant Жыл бұрын

😊👍

@fachryramdhani471 3 жыл бұрын

Hallo Sir, how if I wanna use multiple criteria for instance "=Sumproduct(1/countifs(...))"?

@Innozant 3 жыл бұрын

You may check Playlist Interview crack recent episode crack jobs #24 q4 ,you may get the answer for the same.🙏❤️

@fachryramdhani471 3 жыл бұрын

@@Innozant Okay. I will check it. Thank you.

@AjayKumar-ju4mf 2 жыл бұрын

Sir how to get distinct count by formula ??

@sumitbhadrecha8211 7 ай бұрын

Sir How we count unique in filtered column having with mixed data #N/A

@sumitbhadrecha8211 7 ай бұрын

Like column values 101, 103, #N/A, 101, 105. If we want to get unique value in filtered how we can get it.

@guddusingh-dl2mw 2 жыл бұрын

Sir agar hmaari range pure column ki hai A:A h tab kya karrnge.

@gear02 2 жыл бұрын

Please help me unique count but only visible cell

@dileepsant1606 Жыл бұрын

Sir yadi hame date wise unique value nakalana ho to kaise mikale agar hamare pass ek sal ka Data ho

@Harishckt1 Жыл бұрын

HiI have more than 50 Lakhs Products IDs and i need to findout duplicates out of this huge data. Pls assist with formula.

@dqfashionfactory6470 2 жыл бұрын

Need some knowledge in Web excel

@Innozant 2 жыл бұрын

Ok .topic please ..will guide you

@dqfashionfactory6470 2 жыл бұрын

@@Innozant sir, we do have lot of works in sharepoints with multiple sheets, please guide us to report connection with 3 multiple sheets in one pivot.

@newthoughts9184 6 ай бұрын

Isi farmule main fillter lagana batayiye

@Innozant 6 ай бұрын

Filter bahar chala dijiye

@sitaramkohli9549 3 жыл бұрын

👌🙏🙏

@Innozant 3 жыл бұрын

🙏♥️

@RudraStarP 3 жыл бұрын

Sir me bhi study kar Raha hun excel ki me mis Executive hun me bhi apni video dalna chahta hun sir so please suggest me I am continuing watching your videos from long time

@Innozant 3 жыл бұрын

Achi Baat hai,First of All Apna Vision plan kijiye .Aur uspe kaam kijiye..Jo kaam start karen tbhi karen jab usme passion ho aur aap usko enjoy karte hon..To bahut acha kar sakte hain life me.

@shantisawaroopsharma877 2 жыл бұрын

Sir if I want to count Mondays or any other day present/absent only in an attendance sheet. How can I do it in excel. Kindly consider this urgent. Make a video on this topic

@Innozant 2 жыл бұрын

Kindly watch excel maharaj series..Your related solutions are there

@dibyohere 3 жыл бұрын

how use this function for filtered data in a table ?

@Innozant 3 жыл бұрын

There is no issue either we are applying on filtered or non filtered data

@dibyohere 3 жыл бұрын

@@Innozant nooo the result do not changes accordingly when the data is filtered.

@syedfaaiqehusain3965 Жыл бұрын

Easiest way to count unique values would be to insert the pivot table for the given table. Now, select the desired column from pivot fields in the row area. This will show only the unique values which can be easily counted by using Count.

@Innozant Жыл бұрын

But we want formula based solution 😊👍

@kaaminikannoujia5730 2 жыл бұрын

Agar ek data dusri sheet pr same chahiye pr unique sirf headers hai or koi id ya aisa kuchh unique nhi hai to kaise lekr ayenge..

@pksrichandan226 3 жыл бұрын

How to count unique value in a column active cells like subtotal formula?

@Innozant 3 жыл бұрын

May be video is already there in tips and tricks playlist

@pksrichandan226 3 жыл бұрын

@@Innozant Sir, would you please write the formula please. Regards

@RudraStarP 3 жыл бұрын

Jahan pr error a raha h bahan par if error ka bhi use ya fir if blank ka bhi use kar sakte h sir

@Innozant 3 жыл бұрын

Bikul iferror use kar lijiye ya..if aur uske inside iserror bhi kar sakte hain..better rhega koi bhi formula lagayen to uske bahar iferror jarur lagayen.

@Innozant 3 жыл бұрын

But is Question ke case me iferror se solution nhi ho payega jo hum chahte hain..hope aapko aajke video pasand aaye honge aur apne seekha hoga .