sum numbers under or over a criteria?

D

Dan B

Hi,

I have a column of numbers. I need one formula to sum all numbers that are
under 8 and another formula to sum them if they are over 8.

Thanks
 
P

Pete_UK

Assume your numbers are in A1:A10, try this:

=SUM(IF(A1:A10<=8,A1:A10)), and
=SUM(IF(A1:A10>8,A1:A10))

These are array formulae, so once you have typed them in (or
subsequently edit them) you must commit them with CTRL-SHIFT-ENTER
instead of the normal ENTER. If you do this correctly then Excel will
wrap the formulae in curly braces { } when viewed in the formula bar -
you must not type these yourself.

The first one adds numbers less than or equal to 8, the second one
adds numbers greater than 8.

Hope this helps.

Pete
 
D

Dan B

That does help...Thank You!!


Pete_UK said:
Assume your numbers are in A1:A10, try this:

=SUM(IF(A1:A10<=8,A1:A10)), and
=SUM(IF(A1:A10>8,A1:A10))

These are array formulae, so once you have typed them in (or
subsequently edit them) you must commit them with CTRL-SHIFT-ENTER
instead of the normal ENTER. If you do this correctly then Excel will
wrap the formulae in curly braces { } when viewed in the formula bar -
you must not type these yourself.

The first one adds numbers less than or equal to 8, the second one
adds numbers greater than 8.

Hope this helps.

Pete
Click to expand...
 
Top