Countif not to include duplicates

[Noah101]

How do I set a countif but dont want to count duplicate names? Example a sales team of 5 sold x number of iteams but then Adams name may come up several times due to mutiple things sold. I just want the countif to count name once?

example below

Sorry having some issues in upload I'll give you an example

Raw data
Colomn A (iteam sold) Colomn B (name)
1 Ford Mustang David
2. Ford Mustang. David
3. Jeep Cherokee Kevin
4. Audi Q5. Kevin
5 Audi Q5 John
6. BMW X3 John
7. Bmw X1 Priya
8, Audi Q5 Jaspreet

Front Dashboard (structured data) formulas will be here
Col A. Col B will have the formula which gives the number
Bmw 2
Audi 3
Jeep 1
Ford Mustang 1


Like the above,the countif will do that

Hope that helps,

Thanks everyone!

 

[Aladin Akyurek]

Re: CoUntif not to include duplicates

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Data!$B$2:$B$400=""),IF(ISNUMBER(SEARCH("bmw",Data!$B$2:$B$400)),MATCH(Data!$B$2:$B$400,Data!$B$2:$B$400,0))),ROW(Data!$B$2:$B$400)-ROW(Data!$B$2)+1),1))

 

[Noah101]

Thank you Aladdin this is very close but I think I need explain more where I need the formula, I have a front dashboard which is horzontal and in the first category it's called BMW then the cell just under it is staff count then the next box is Audi then just under there is staff count, so I need the formula to go here then the formula whoud check the second tab where the raw data is colomn A is names in like 300 rows vertical and B is the car vertical also about 300 rows. I need the formula to go to this big raw data to check how many staff sold each but only unique values no duplcates. So if David sold bmw 5 times I would count that has 1 I. The main horizontal dasboard.

Thanks!

 

[Noah101]

Thank you Aladdin this is very close but I think I need explain more where I need the formula, I have a front dashboard which is horzontal and in the first category it's called BMW then the cell just under it is staff count then the next box is Audi then just under there is staff count, so I need the formula to go here then the formula whoud check the second tab where the raw data is colomn A is names in like 300 rows vertical and B is the car vertical also about 300 rows. I need the formula to go to this big raw data to check how many staff sold each but only unique values no duplcates. So if David sold bmw 5 times I would count that as one in the main horizontal dashboard under Bmw

Thanks!

 

[Aladin Akyurek]

The formula does exactly what you ask for... Did you try it?

In

=SUM(IF(FREQUENCY(IF(1-(Data!$B$2:$B$400=""),IF(ISNUMBER(SEARCH("bmw",Data!$B$2:$B$400)),MATCH(Data!$B$2:$B$400,Data!$B$2:$B$400,0))),ROW(Data!$B$2:$B$400)-ROW(Data!$B$2)+1),1))


replace the sheet name Data with the name of your "second tab". Replace also "bmw" with the cell of your "front dashboard" which houses this "category".

​

<strike></strike>

 

[Noah101]

I will try again, but how does this formula compare the cars column to the name colomn I only see B in your formula but does it not need to look at A and B names and cars?

 

[Aladin Akyurek]

I will try again, but how does this formula compare the cars column to the name colomn I only see B in your formula but does it not need to look at A and B names and cars?

Right...

=SUM(IF(FREQUENCY(IF(1-(Data!$B$2:$B$400=""),IF(ISNUMBER(SEARCH("bmw",Data!$A$2:$A$400)),MATCH(Data!$B$2:$B$400,Data!$B$2:$B$400,0))),ROW(Data!$B$2:$B$400)-ROW(Data!$B$2)+1),1))

 

[Noah101]

Hi Aladdin thank you! I just want to say I figured this one out and yes you are correct! This is the formula sumif frequency but because of the set up of my data I needed to adjust it just a little. But its solved now thank you for helping getting me towards right path.

 

[Aladin Akyurek]

Hi Aladdin thank you! I just want to say I figured this one out and yes you are correct! This is the formula sumif frequency but because of the set up of my data I needed to adjust it just a little. But its solved now thank you for helping getting me towards right path.

You are welcome. But what is the end formula that you have implemented?