Solved

SumIF Nested Formula

Posted on 2012-03-20
2
363 Views
Last Modified: 2012-03-20
=SUMIF(Sheet1!K:K, ">30"&"<=60",Sheet1!N:N)

Above is my formula.  Which is not functioning.
I need the sum of all values in N where the value in K is >30 and <=60

K

Age
201
77
112
214
63
36
406
356


Age
50


Age
111


Age
138


Age
70


Age
355
78
36
68
97
22
97



N


Cost
$8440
$4000
$9640
$14675
$19985
$20295
$15760
$16490


Cost
$12500


Cost
$8600


Cost
$12000


Cost
$14200


Cost
$25390
$6425
$11195
$10500
$12065
$19050
$17460
0
Comment
Question by:swedishmotors
[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
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 280 total points
ID: 37743381
Which version of excel are you using? In Excel 2007 or later you can use SUMIFS (with an "S" on the end) i.e.

=SUMIFS(Sheet1!N:N,Sheet1!K:K, ">30",Sheet1!K:K,"<=60")

in earlier versions try subtracting one SUMIF from another like this

=SUMIF(Sheet1!K:K, ">30",Sheet1!N:N)-SUMIF(Sheet1!K:K, ">60",Sheet1!N:N)

Note the criteria needs to be reversed in the second one because you are subtracting.....

regards, barry
0
 
LVL 1

Author Closing Comment

by:swedishmotors
ID: 37743598
Thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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