CODEWITHSUNDEEP
excelexcel-formula
Gert Kommer
Gert Kommer

Reputation: 1243

Excel count columns where values match and value is above threshold

Im trying to find matches between column B and C when the value in A is above a certain threshold.

0.99    p269    p269
0.99    p312    p312
0.64    p249    p249
0.64    p247    p247
0.09    p243    p284

I'm trying the Countifs method but it doesnt work.

=COUNTIFS(
        A1:A31968,">" & F2,
        B1:B31968,C1:C31968
)

The first part works (F2 is my treshold), but the I want to check all rows. So when my threshold is 0.5 I want 4 as a result. When the threshold is 0.08 I still want 4 because the labels of the fifth row don't match. How do I do this?

Upvotes: 1

Views: 156

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

The other option is to use a pseudo-array formula

=SUMPRODUCT((A1:A5>F2)*(B1:B5=C1:C5))

to combine the two conditions. It doesn't have to be entered as an array formula, but may have performance issues if used on several thousand rows of data.

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

One option would be to add a fourth column to spreadsheet in column D containing the following formula:

=IF(B1=C1, 1, 0)

Here is what your spreadsheet looks like now:

A       B       C       D
0.99    p269    p269    1
0.99    p312    p312    1
0.64    p249    p249    1
0.64    p247    p247    1
0.09    p243    p284    0

In other words, if columns B and C agree, there is a 1 otherwise 0. Then, you can use the following COUNTIFS formula:

=COUNTIFS(A1:A5,">0.5",D1:D5,"=1")

Here we check the 0.5 threshhold on column A as you were already doing, but we also check that the B and C values are in agreement.

Upvotes: 2

Related Questions