Count range values but not duplicates

[trimiii]

Hi,

I have the challenge to find the correct formula for the following:

I am currently using=COUNTIFS('KNK K Project'!A:A, {"BENZ"}, 'KNK K Project'!O:O, {"<=9"}) which it works for the total count.

So column "A:A" is my vehicle brand (Benz), and the column "O:O" are days open for service order. Now in column "C:C" I have the service work order number which has duplicates (because of awaiting parts and maybe one service order is listed 4 times).

What I am struggling with is, to find the formula to count my total number of the service order is listed in column "C:C", if criteria is "BENZ" (column "A;A"), with value <=9 (column "O:O") but not to count the duplicates in column "C:C"

Thoughts...

 

[Peter_SSs]

Thoughts...

... update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[trimiii]

Hi Peter, thank for note and I updated the data and I am currently using:
Product information:
Microsoft Office ProPus

 

[vmjan02]

hi trimiii,
take a supporting column and check for duplicate in column C:C, if there is no duplicate in supporting column the put your count formula

 

[trimiii]

Hi vmjan02

Can you help me with the correct formula, please?

This is what I am using for the total count...
=COUNTIFS('KNK K Project'!A:A, {"BENZ"}, 'KNK K Project'!O:O, {"<=9"})

 

[Peter_SSs]

I updated the data and I am currently using:
Product information:
Microsoft Office ProPus

OK, thanks. If your version of 365 has the FILTER & UNIQUE functions, then try something like the following.
I think is is best to avoid whole column references as some functions take that literally and try to calculate 1,000,000+ rows when often only a few hundred or few thousand rows are actually being used.
I have allowed for 1000 rows below but you adjust to be reasonable for the amount of data you are actually expecting.

trimiii 2020-03-11 1.xlsm
	A	B	C
1
2		3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNT(UNIQUE(FILTER('KNK K Project'!C2:C1000,('KNK K Project'!A2:A1000="Benz")*('KNK K Project'!O2:O1000<=9))))

The above formula is counting the green rows below and excluding the blue duplicates.

trimiii 2020-03-11 1.xlsm
	A		C												O
1
2	Benz		1												6
3	Audi		2												14
4	Benz		1												5
5	Audi		3												7
6	Benz		1												7
7	Benz		2												12
8	Benz		2												8
9	Audi		3												11
10	Benz		2												1
11	Porsche		5												5
12	Porsche		6												6
13	Audi		7												5
14	Benz		8												9
15
KNK K Project

 

[trimiii]

Hi,

Checked my functions in excel and unfortunately, I don't have Unique and Filter functions because once I enter the formula I receive the message "That function isn't valid" (see picture attached).

Any other idea, please?

 

[Peter_SSs]

I don't have Unique and Filter functions

Pity, but you may well have them within a few months - not sure of just when though.

Unfortunately, the alternative that I came up with without using a helper column (see cell C3) is a bit more long-winded.

trimiii 2020-03-11 1.xlsm
	A	B	C	D
1
2		3	3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=COUNT(UNIQUE(FILTER('KNK K Project'!C2:C1000,('KNK K Project'!A2:A1000="Benz")*('KNK K Project'!O2:O1000<=9))))
C2	C2	=COUNT(IF('KNK K Project'!A2:A1000="Benz",IF('KNK K Project'!O2:O1000<=9,IF(MATCH('KNK K Project'!A2:A1000&"|"&'KNK K Project'!C2:C1000&"|"&('KNK K Project'!O2:O1000<=9),'KNK K Project'!A2:A1000&"|"&'KNK K Project'!C2:C1000&"|"&('KNK K Project'!O2:O1000<=9),0)=ROW('KNK K Project'!A2:A1000)-ROW('KNK K Project'!A2)+1,1,""))))

If you are happy to use a helper column in 'KNK K Project' then try this.
Note that the helper column can be hidden once the formula has been entered & copied down.

trimiii 2020-03-11 1.xlsm
	A	B	C												O	P
1
2	Benz		1												6	1
3	Audi		2												14
4	Benz		1												5
5	Audi		3												7
6	Benz		1												7
7	Benz		2												12
8	Benz		2												8	1
9	Audi		3												11
10	Benz		2												1
11	Porsche		5												5
12	Porsche		6												6
13	Audi		7												5
14	Benz		8												9	1
15
KNK K Project

Cell Formulas
Range	Formula
P2:P14	P2	=IF(COUNTIFS(A$2:A2,"Benz",C$2:C2,C2,O$2:O2,"<=9")=1,1,"")

trimiii 2020-03-11 1.xlsm
	D
1
2	3
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNT('KNK K Project'!P2:P1000)

 

[trimiii]

Wow, this works just fine, thanks a lot and appreciate your assistance and support.

 

[Peter_SSs]

Wow, this works just fine, thanks a lot and appreciate your assistance and support.

You're welcome. Thanks for the follow-up.