Excel - COUNTIF(s) & SUMPRODUCT-CodePudding

I'm trying to count clusters of values in one column but only if a value in another column is above a certain value.

I started with the below code to count how many unique clusters were in a column.

=SUMPRODUCT(1/COUNTIF(B1:B10,B1:B10))

| A        | B              |
| -------- | -------------- |
| 50       | 1              |
| 200      | 1              |
| 190      | 2              |
| 10       | 5              |
| 100      | 1              |
| 70       | 5              |
| 130      | 2              |
| 10       | 5              |

This would return a value of 3 as there are 3 unique clusters (1,2,5)

However, I am wanting to add a dependacy based on column A. Only count clusters in B if A>100. As there are no values of 5 in column B where A>100, the cluster count in B would be 2.

Any help to achieve the above would be very much appreciated!!

CodePudding user response：

With Microsoft365:

=COUNT(UNIQUE(FILTER(B1:B8,A1:A8>100,"")))

With older versions:

=SUMPRODUCT((A1:A8>100)*IFERROR(1/COUNTIFS(A1:A8,">100",B1:B8,B1:B8),0))

CodePudding user response：

For any version:

=COUNT(1/(FREQUENCY(IF(A1:A8>100,B1:B8),B1:B8)))

array entered (with Ctrl Shift Enter) if non-365. If you have 365 use JvdV's answer. :)