Counting Distinct Values

[ShaneL79]

Hello,

I am stuck on an issue where I count distinct values. Hopefully my explanation is clear enough.

Example Data:

EVENT #	SYSTEM	EVENT TYPE	EVENT SEVERITY	EVENT RANGE
100	A	FAIL	S8	USED
100	A	FAIL	S8	USED
101	A	FAIL	S8	USED
102	A	FAIL	S8	USED
103	A	FAIL	S8	USED
103	B	FAIL	S8	USED
103	B	FAIL	S8	USED
103	B	FAIL	S8	USED

For the sake of this data, I showed EVENT TYPE, EVENT SEVERITY, and EVENT RANGE as a single value. These do have multiple values, but I know how to filter by value. It is counting the distinct EVENT # for the following criteria:

COUNT

• [DISTINCT EVENT #]

WHERE

• SYSTEM = A
• EVENT TYPE = FAIL
• EVENT SEVERITY = S8
• EVENT RANGE = USED

Based on the above date, the value would be "4". Assuming I am using the word "distinct" correctly, I want any groupings with similar EVENT # to count as 1. Using the above data, this means the 4 items are 100, 101, 102, and 103.

Any idea?

Thank you.

 

[iggydarsa]

This might give you some ideas

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

22 12 02.xlsm
	A	B	C	D	E	F	G	H
1	EVENT #	SYSTEM	EVENT TYPE	EVENT SEVERITY	EVENT RANGE		SYSTEM	A
2	100	A	FAIL	S8	USED		EVENT TYPE	FAIL
3	100	A	FAIL	S8	USED		EVENT SEVERITY	S8
4	101	A	FAIL	S8	USED		EVENT RANGE	USED
5	102	A	FAIL	S8	USED		COUNT DISTINCT	4
6	103	A	FAIL	S8	USED
7	103	B	FAIL	S8	USED
8	103	B	FAIL	S8	USED
9	103	B	FAIL	S8	USED
Count Unique

Cell Formulas
Range	Formula
H5	H5	=COUNT(UNIQUE(FILTER(A2:A9,((B2:B9=H1)*(C2:C9=H2)*(D2:D9=H3)*(E2:E9=H4)))))