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

#### roobydoo

##### Board Regular
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### gsistek

##### Well-known Member
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

##### Well-known Member
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))

Last edited:

##### MrExcel MVP
Control+shift+enter, not just enter:

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

#### roobydoo

##### Board Regular
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

##### Board Regular
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!

##### MrExcel MVP
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

##### Board Regular
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?

##### MrExcel MVP
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

##### Board Regular
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!

Replies
0
Views
371
Replies
2
Views
198
Replies
1
Views
188
Replies
4
Views
126
Replies
0
Views
141

1,191,190
Messages
5,985,201
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back