Counting Unique Outcomes

[cotech_10]

Hi All,

I using an Excel Function Formula where I am trying to achieve a particular outcome. The current Formula looks like this:

=SUMPRODUCT(--(COUNTIF(O3:Y3,$O$4:$Y$4)>0))


I have two sets of data as per Example 1 below:

1. Range Data
2. Criteria Data

What I am currently doing is counting the unique only appearances found
in the Range Data against the Criteria Data


So for example the count of those events from the above data would = 7
Broken down as follows:

Range Data 200 Count = 1 (even though it appears more than once in the
Range Data Table we are only counting its unique
appearance against the Criteria Table

Therefore for the other Data in the Range Data Table we have:

300 - appears once in Range Data Table but appears in the Criteria Data Table Count = 1
400 - appears once in Range Data Table but appears in the Criteria Data Table Count = 1
500 - appears once in Range Data Table but appears in the Criteria Data Table Count = 1
600 - appears once in Range Data Table but appears in the Criteria Data Table Count = 1
70 - appears once in Range Data Table but no appearance in the Criteria Data Table Count = 0
800 - appears once in Range Data Table but appears in the Criteria Data Table Count = 1
99 - appears twice in Range Data Table but appears in the Criteria Data Table Count = 1
111 - appears once in Range Data Table but no appearance in the Criteria Data Table Count = 0


This all works fine with Contiguous Criteria Data but what I would like to achieve is to
select non adjacent Criteria Data and perform the same Unqiue Count as above

For example as per the Image below:

In this example I have selected Criteria Values of "120","200", "500", "300", and "800" highlighted in Orange and
resulting in a Unique Count of "4"

I look forward in hearing from someone around this request




Regards

 

[KRice]

If you want to select the cells of interest, you will need a VBA solution, as a formulaic approach will not be able to recognize the selected cells. If you are interested in a formula-based approach, then you might consider using a table to specify which items in the main Criteria Data array should be included...as shown in the following where examples 1, 2, 3, and 4 use the numeral 1 to specify keeping the corresponding item from the main criteria array. Your original formula is slightly modified to construct the new criteria array. One concern with this approach is the two arrays are multiplied, so you will get 0's in the new array, and if 0 is ever a component of your main Range array, this will likely generate an incorrect count. A possible work-around is to consider using x's (or some other specifier) as shown in examples 3b and 4b. Here, your original formula is modified with an IF statement to generate the new criteria array, and blanks are introduced if the main criteria array item is not to be included.

MrExcel_20230523.xlsx
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3	Range Data	200	300	400	500	600	700	800	99	99	111	200
4	Criteria Data	120	200	150	500	400	999	99	450	600	300	800
5
6	Unique Count	7
7
8	Range Data	200	300	400	500	600	700	800	99	99	111	200
9	Criteria Data	120	200	150	500	400	999	99	450	600	300	800	Unique Count
10	Criteria Example 1	1	1	1	1	1	1	1	1	1	1	1	7
11	Criteria Example 2	1	1		1						1	1	4
12	Criteria Example 3		1	1			1			1			2
13	Criteria Example 4					1		1	1		1		3
14	Criteria Example 3b		x	x			x			x			2
15	Criteria Example 4b					x		x	x		x		3
cotech_10

Cell Formulas
Range	Formula
O6	O6	=SUMPRODUCT(--(COUNTIF(O3:Y3,$O$4:$Y$4)>0))
Z10:Z13	Z10	=SUMPRODUCT(--(COUNTIF($O$8:$Y$8,($O$9:$Y$9)*($O10:$Y10))>0))
Z14:Z15	Z14	=SUMPRODUCT(--(COUNTIF($O$8:$Y$8,IF($O14:$Y14="x",$O$9:$Y$9,""))>0))

 

[cotech_10]

Hi Kirk,

Thank you for your efforts and response.. You are correct in advising that this would be better suited with a VB solution,
but I will go forward with your solution as above and see how I go with it.

Once again thanks again for your efforts it is much appreciated.

 

[KRice]

I'm happy to help. Please post back if you run into any difficulties.

 

[AVO]

I'm happy to help. Please post back if you run into any difficulties.

Hi Kirk,
Is there any chance of checking out my question I have posted?
Thanks!