CODEWITHSUNDEEP
excel
Giang Nguyen
Giang Nguyen

Reputation: 485

Check if a range contains a value > 0 in excel

In excel, i would like to check this range B2:G2, whether any cell within this range contains a value > 0, then it will returns True, else False.

Is there an excel formula that can help to achieve this? Before i am asking this question, tried different approach but no help such as: counta, sumproduct...

Upvotes: 1

Views: 9967

Answers (3)

objectively C
objectively C

Reputation: 1008

Yes! There is an excel formula to achieve this.

=IF(COUNTIF(B2:G2,">0"),"True","False")

Source: https://exceljet.net/formula/value-exists-in-a-range enter image description here

Upvotes: 2

Cedric Vongheer
Cedric Vongheer

Reputation: 31

Well if you don't want to use VBA the code below will work (although it is ugly ...)

    =IF(AND(ISNUMBER(B2),B2>0),TRUE,
IF(AND(ISNUMBER(C2),C2>0),TRUE,
IF(AND(ISNUMBER(D2),D2>0),TRUE,
IF(AND(ISNUMBER(E2),E2>0),TRUE,
IF(AND(ISNUMBER(F2),F2>0),TRUE,
IF(AND(ISNUMBER(G2),G2>0),TRUE,FALSE))))))

It is just a bunch of nested if functions but it gets the job done

Upvotes: -1

jsheeran
jsheeran

Reputation: 3037

The MAX() formula can be applied to a range. Therefore, =MAX(B2:G2)>0 will give you your answer.

Upvotes: 4

Related Questions