Solved

Sumif

Posted on 2014-03-15
4
265 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now