Excel Magic Trick 368: Count Unique Items With More Than One Criteria 

Thank you! I am glad that the videos help. I try to have fun while making a difference! Have you ever had to do a "unique count with criteria in a different column", like seen in this video?

I was looking for something like this! Got at least 30 videos watched about "almost stuff"... Something like 8 hours. Thank you for the help!

Thank you for all the time and effort you put into these videos. As one of the few people who know excel in my office, I'm often nominated to "make it happen" when we receive a request for special reports from upper management. The videos that you have Mr. Excel have produced have been a tremendous help. Thank you for all you do. You make a difference.

Mr. Excel --- just want to let you know that you ROCK man ... I every excel question I have you have tackled! --- cheers mate!

I am glad that the trick can help!

I've viewed this video a number of times and your explanation of the process of finding uniques for the labels is so clear. Thanks so much for sharing your knowledge with us.

Hi, just writing to thank you for your tremendous videos that have really helped me with my PhD research. This one in particular got me out of a real bind. I had to count unique species names given 3 criteria. My problem was slightly more complicated than your example and my initial attempt at solving it was incorrect but with a little lateral thinking and consulting your many other videos I consolidated my criteria columns using concatenation (a very cool trick) and lo and behold it worked!!!

No=NO=no=nO, so no caps don't matter. Did you try downloading the workbook - it has an example of the correct formula. In your formula you have single cells like K2=$M$2 and L2, and in the formula in the video ranges of cells are used.

I am glad that the videos help! --excelisfun

Hello: your videos are so helpful. Regarding the counting unique items; I need to count unique items with more than two criteria. In my example, I am looking for a count of unique account numbers for each employee for each month. So, in other words, for each month of the year how many unique accounts did each employee work with? Apologies if this is already posted, I'm not finding it.

Thanks bud. I lost you somewhere in the explanation but followed along as best as I could. In the end it worked so I dont have to understand it lol. Appreciate the video!

Thanks sir very helpful video

Thank you - I've been referring to your videos for a couple years now... always excellent!

What if we have a condition to calculate sum of unique values of a column if two conditions are fulfilled: 1. Sun of First column range >0 , 2. Sum of second column range being a1,a3,a3 and a5. What can be single formula depicting this requirement

Hi, just found your channel, very informative. I was wondering if this could be applied to unique dates?

Yes, I am actually working on a report and work that requires it, although I need to limit results to a date range as well.

Quite informative and very similar to EMT 627. Great video. 👍

I wish I was as good as you at Excel

That's a great help, thanks. However, in my case there is more than one condition to check with if function hence I used IF twice and got the result...

This is great, but what would I do if I wanted to add another unique criteria. So not just column H but where H (First Name) and I (Last Name) voted "Yes".

Thanks, just what I need to do. However, using 365 so wondering if there is a neater way of doing it without the CSE?

Dear Sir I appreciate your insight with the Excel I have Query with Countifs of Two-column for counts=COUNTIFS(H58:H64,"

do you have a video of how to get this code to run from vba macro? Thanks

By using office 365, is there any way to solve the problem differently?

The only other related video for counting unique using the FREQUENCY function is this title: Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)

@LCTECH001 , Thank you for being so persistence!

What if there are 2 columns that you need to meet and then count the one that is on the third column? it is similar to what is above adding one more criteria to it.

Something like: =SUM(IF(FREQUENCY(IF(B3:B13=F3,MATCH(C3:C13&D3:D13,C3:C13&D3:D13,0)),ROW(D3:D13)-ROW(D3)+1),1)) Where first and last name columns are joined with & in MATCH. I will make a video on this topic soon. Look for it.

How to return the unique list?

I love your videos. I need some help counting all cells in which the last 2 digits on the number are greater than 40. How could I do that?

i dont understand i try to mimic exactly what you are doing with the same data and i get all ones when i try to get the total individuals that voted yes (the second problem that you do). do i have to put the no's and yes's in caps? i shouldnt have too

Wow! That is great to hear -- I am glad that this trick and the concatenated helper column did the job! If you send me (excelisfun at gmail) a small data set similar to your "count unique species names given 3 criteria" and give me a brief description of what you are doing, I will make a video for the benefit of other RU-vidr Excelers!

dear sir, i want to know, how to count the difference text in the same row or column? please help me,

I have a input data form that I use to input data as a Heavy equipment registry. Work is granted fairly across the board for all sub-contractors, I have the data input form that enteritis the names of the company's into a work sheet, I have a toggle button for work granted either "yes" or "no", next to those toggles I have a text box to record the amount of "yes" and the amount of "Mo" each company receives. once entered they will receive a no. However if in the future they are granted work I want when I up date their records that the txt box next to the toggle button to record how many "yes" the other to record how many "No" they have, so if their record is recalled it can show that result. It should be noted that the data has the company listed several times depending on the different types of equipment they have, some may have 7 dump trucks, several excavators. So no matter the number of entries for that company, I will still get how many "no's" and "yes's". I don't know the coding but....something like if txtowner.value =xx then Txtyes.value = cell.range F:F where xx and yes is true...something to that effect.

How to count number of word in whole row or column?

@LCTECH001 , I am sorry, I do not know how to solve that.

I'm so sad right now. When I click enter i get this message "You’ve entered too few arguments for this function. To get help with this function, click ok to close this message. Then click the insert function located to the left of the equal sign in your formula." Help! ):

i dont understand i try to mimic exactly what you are doing with the same data and i get all ones when i try to get the total individuals that voted yes (the second problem that you do). do i have to put the no's and yes's in caps? i shouldnt have too. im typing every thing ExaCTLY AS YOU HAVE IT OBVIOUSLY HIGHLIGHT MY CELLS WHICH ARE numbered differently....=IF(K2=$M$2,MATCH(L2,$L$2:$L$12,0))

Where can I download the workbooks used in these awesome videos? Thank you, Dave

amazing!

if ay of 'site' = 'blank', your formula is helpless, sorry

@LCTECH001 , I do not know without looking at data set and formula. Post Question to: mrexcel [dot] com/forum You can send me link to your post after you post.

@LCTECH001 , I cannot find the post. Send full link to excelisfun at gmail

I look

I made a video for you: Excel Magic Trick 1027: Array Formula To Count Unique Yes Votes For A Given First & Last Name youtube [dot] com/watch?v=WfRgnebu6aY

I think tha I got it, simply added the additional criteria: SUM(--(FREQUENCY(IF(Data!$A:$A=$B4,IF(Data!$D:$D=7,Data!$B:$B)),Data!$B:$B)>0))