Dynamic Criteria when using COUNTIF() on Range?

[guy0087]

I am using the following countif() function on a range of cells =COUNTIF(D7:R7,">0")

My question is, I don't necessarily want to use 0, but would rather use a number that is in the same column but a different row for each value in D7:R7. In this example, the data is in row 5. How would I specify this in the criteria portion of the function? I tried =COUNTIF(D7:R7,">"&D5:R5) but that's not getting the job done.

Any ideas?

 

[Jonmo1]

Welcome to the board.

Try
=SUMPRODUCT(--(D7:R7>D5:R5))

 

[guy0087]

Wow, thank you. After reading about the -- and the SUMPRODUCT function I'm still not entirely sure why it works, but it does. I wound up using a percentage of the value in row 5 so it came out looking like this =SUMPRODUCT(--(D7:R7>(D5:R5)*0.25)).

Thank you so much for the help!

 

[Jonmo1]

Glad to help, thanks for the feedback.