Sumif

Got this from Ken on EE to sum all numbers less than a value specified in a cell (P1118)

=SUMIF(P5:P1115,"<"&$P$1118)

but now need to modify to only sum positive numbers (greater than zero).

Thanks in advance,

swjtx99
swjtx99Asked:
Who is Participating?
 
Ken ButtersCommented:
=SUMIFS(P5:P1115, P5:P1115, "<"&$P$1118, P5:P1115,">0")

Open in new window

Note the extra "S"... =SUMIFS rather than =SUMIF

you have range to sum....  P5:P1115

Followed by pairs of parameters range / criteria :

<criteria range> <criteria>    P5:P1115, "<"&$P$1118
<criteria range> <criteria>    P5:P1115,">0"

The first set says where the value in P5:P1115 is less than the value in P1118
The second says where the value in P5:P1115 is greater than zero.
0
 
Ken ButtersCommented:
Is this what you are looking for?

=SUMIF(P5:P1115,">0")
0
 
swjtx99Author Commented:
Sorry, I wasn't specific enough. I need to sum numbers in P5:P1115 greater than zero (no negative numbers) but less than the value in Cell P1118.
0
 
swjtx99Author Commented:
Thanks again Ken,

Seeing it written out, it looks simple but I'd never heard of SUMIFS. Well you learn something new every day!

swjtx99
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.