2

This is something I've been struggling with. I made sure to check other threads here and google additional solutions but I couldn't find exactly what I was looking for.

Basically, I want to determine duplicate numbers in a row (total rows are >40,000) but only targeting numbers that are greater than 0. Screenshot

So as you can see, the first row has some numbers under columns C1, C2, C3, C4 and C5. Two of them are greater than 0 AND duplicates.

However in the second row there are no duplicate values that are greater than 0.

I can't figure out what formula I should use that would yield the following result: Expected result enter image description here

I would really appreciate your assistance with this.

Avinash
  • 1,245
  • 11
  • 19
Hanma
  • 23
  • 3
  • One suggestion, you might want to rephrase your spreadsheet to spread out your data across rows, rather than columns. I'm not sure if `COUNTIFS` works across columns. – Tim Biegeleisen Aug 24 '18 at 02:12
  • Hi Thank you for the quick response. I've considered that possibility but unfortunately it won't be feasible in this case. – Hanma Aug 24 '18 at 02:21

1 Answers1

0

There are standard 'count if greater than zero' formulas. For example,

=COUNTIF(A2:E2, ">0")

There are standard 'count if greater than zero and unique' formulas. For example,

=SUMPRODUCT((A3:E3>0)/(COUNTIFS(A3:E3, A3:E3)+(A3:E3<=0)))

If one equals the other then 'unique', otherwise 'duplicate.

=IF(COUNTIF(A2:E2, ">0")=SUMPRODUCT((A2:E2>0)/(COUNTIFS(A2:E2, A2:E2)+(A2:E2<=0))), "unique", "duplicate")

enter image description here

  • That seems to work well! Thank you so much. I'll read up on the formulas and your link to understand the logic behind them. – Hanma Aug 24 '18 at 03:46