Count if formula if sum of two cells is less than 8?

[roobydoo]

Hi,
I need a formula that will look at the sum of the cells in each of two adjacent rows and count it if the sum is less than 8. I can add a hidden column that adds the two numbers and then use that column in my formula to count if the answer is less than 8 but I would rather not add the hidden column. Can someone help me write something so I don't have to add a column? In looking at the selection below, I would have an answer of 2. Thank you in advance for looking at this! Roobydoo


4 5
4 4
4 3
4 4
4 4
4 4
3 4

 

[gsistek]

Try:
{=SUM(IF($A$1:$A$10+$B$1:$B$10<8,1,0))}

This assumes that the numbers are in $A$1:$A$10 and $B$1:$B$10

To enter this formula, write it (without the brackets{}) and press CTRL-SHIFT-ENTER instead of just ENTER.

 

[bbott]

SUMPRODUCT will also work if you don't want to use an array formula:

=SUMPRODUCT(--(A1:A10+B1:B10 < 8))

If you have empty cells, the formula will need to be:

=SUMPRODUCT(--(A1:A10+B1:B10<8),--(A1:A10+B1:B10<>0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(MMULT(A2:A8+B2:B8,COLUMN(A2:B8)^0) < 8,1))/COLUMNS(A2:B8)

 

[roobydoo]

SUMPRODUCT will also work if you don't want to use an array formula:

=SUMPRODUCT(--(A1:A10+B1:B10 < 8))

If you have empty cells, the formula will need to be:

=SUMPRODUCT(--(A1:A10+B1:B10<8),--(A1:A10+B1:B10<>0))

PERFECT! it works, there are blank cells so I will use that one. Thanks so much, this will save me lots of time.
Rooby

 

[roobydoo]

Thanks so much for all the answers. I have so much to learn and I am going to play around with all these suggestions - but it works!

 

[Aladin Akyurek]

Thanks so much for all the answers. I have so much to learn and I am going to play around with all these suggestions - but it works!

You said you wanted a count of 2 as result, not 5. Care to elaborate?

 

[roobydoo]

You said you wanted a count of 2 as result, not 5. Care to elaborate?

Yes, there were just two instances in my example that added to less than 8. Do I not make sense, sometimes I have a hard time describing the problem?

 

[Aladin Akyurek]

Yes, there were just two instances in my example that added to less than 8. Do I not make sense, sometimes I have a hard time describing the problem?

See post #2 you skipped,,,

 

[roobydoo]

See post #2 you skipped,,,

You are right. That one didn't work, it gave the opposite answer. Not sure why. By the way, you have helped me a lot over the years with the formulas you have worked out for me. Thanks!