Excel If statement

Question

I have a file that looks like this:

col hea der s   row
A   0   0   0   0
C   1   2   1   0
N   3   2   3   5

I want to write an if statement that says if any of the values in cells D2, G2, J2 or M2 are greater than or equal to 5, count one. ie:

cel D2  G2  J2  M2
col hea der s  row  count
A   0   4   0   0       0
C   1   5   1   0       1
N   10  2   3   5       2

I can't get the logic right at all, so I have a feeling that there is a less complicated way to do it. This is what I have so far:

IF(D2>=5, 1, 0, IF(AND(G2>=5, 2, 0), IF(AND(J2>=5, 3, 0), IF(AND(M2>=5, 4, 0)))))

But this is obviously nowhere close to what I need. It only will tell you if all 4 are there, not anything else. The only way I can figure this is some horrifyingly long if statement. Can anyone point me in a better direction?

countifs might work here... but I've been playing around with it to no avail. — xQbert
– xQbert, Commented Aug 16, 2013 at 19:53

tigeravatar · Accepted Answer · 2013-08-16 19:54:09Z

3

I provided this in a comment, it will accomplish the task you're looking for:

=SUM(D2>=5,G2>=5,J2>=5,M2>=5)

answered Aug 16, 2013 at 19:54

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerry · Accepted Answer · 2013-08-16 19:51:48Z

3

I would have told you to use COUNTIF if you had a continuous range, but, I guess you can do:

=--(D2>=5)+--(G2>=5)+--(J2>=5)+--(M2>=5)

(D2>=5) gives either true or false, and adding -- at the front makes it into 1 or 0.

Adding all those together gives you the count you're looking for :)

You can use:

=(D2>=5)+(G2>=5)+(J2>=5)+(M2>=5)

As well, but I just can't get around adding 'true' and 'false', not yet anyway!

edited Aug 16, 2013 at 19:51

answered Aug 16, 2013 at 19:39

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

5 Comments

tigeravatar Over a year ago

There's no need for all the -- because you're adding the resulting boolean values together anyway. =(D2>=5)+(G2>=5)+(J2>=5)+(M2>=5)

Jerry Over a year ago

@pnuts You can drop the -- as well, I guess I should ^^;

Jerry Over a year ago

@tigeravatar Good point. I sometimes like to see things as number as opposed to true/false ^^;

tigeravatar Over a year ago

Here's another way to write it using, SUM so that you don't have to duplicate parentheses: =SUM(D2>=5,G2>=5,J2>=5,M2>=5)

Jerry Over a year ago

@tigeravatar Smooth! You could probably add that as answer!

Collectives™ on Stack Overflow

Excel If statement

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related