Hello,
I would like to count unique values in one column after specifying for numbers > 0 in another column. I desire a formula, but not a control+shift+enter formula (which are easy to invalidate by accidentally pressing enter or tab).
My dataset is where Jam, Type, and Zone1 are in A1, B1, and C1, respectively, and the data starts in A2, B2, and C2.
I would like to find unique Jam values where Zone1 > 0. The result should be 14.
I've tried the following formula, but it's finding the unique Jam values for each of the unique values where Zone1 > 0 (10, 25,50, and 75), then adding them together.
For instance, the formula below is finding these unique jams, then adding them together for a result of 17, which is not what I want.
where Zone1 = 10, there is 1 unique Jam;
where Zone1 = 25, there are 9 unique Jams;
where Zone1 = 50, there are 5 unique Jams;
where Zone1 = 75, there are 2 unique Jams;
=SUMPRODUCT((C2:C29>0)/COUNTIFS(A2:A29,A2:A29,C2:C29,C2:C29))
Jam Type Zone1
1 xs 25
1 s 75
2 xs 25
2 s 25
3 xs 0
3 s 0
4 xs 0
5 xs 50
6 xs 25
7 xs 10
7 s 0
8 xs 50
9 xs 25
9 s 0
10 xs 25
10 s 25
11 xs 0
11 s 0
12 xs 50
12 s 25
13 xs 25
13 s 0
14 xs 25
15 xs 0
16 xs 50
16 s 75
17 xs 25
18 xs 50
Thank you for your assistance.
Bookmarks