Solved

Sumif

Posted on 2014-03-15
4
271 Views
Last Modified: 2014-03-16
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
0
Comment
Question by:swjtx99
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39932054
Is this what you are looking for?

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

Author Comment

by:swjtx99
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

by:
Ken Butters earned 500 total points
ID: 39932147
=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
 

Author Closing Comment

by:swjtx99
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 various types of data validation for different data types in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question