Solved

# Sumif

Posted on 2014-03-15
273 Views
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).

swjtx99
0
Question by:swjtx99
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 19

Expert Comment

ID: 39932054
Is this what you are looking for?

=SUMIF(P5:P1115,">0")
0

Author Comment

ID: 39932128
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

LVL 19

Accepted Solution

Ken Butters earned 500 total points
ID: 39932147
``````=SUMIFS(P5:P1115, P5:P1115, "<"&\$P\$1118, P5:P1115,">0")
``````
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

Author Closing Comment

ID: 39932572
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Order table with macro 3 98
Automatic sort formula 8 69
Excel 2007 Macro to Change Column Formatting 3 51
Excel Data Validation Help 9 58
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micrâ€¦
Viewers will learn the basics of using filtering and sorting in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.
###### Suggested Courses
Course of the Month5 days, 13 hours left to enroll