Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SumIF Nested Formula

Posted on 2012-03-20
Medium Priority
370 Views
=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
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

LVL 50

Accepted Solution

barry houdini earned 1120 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

ID: 37743598
Thanks!
0

## Featured Post

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month5 days, 20 hours left to enroll