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

x
Solved

# Using Indirect in Sumif

Posted on 2014-03-30
Medium Priority
249 Views
I am trying to modify a SumIf using "Indirect" to return the the sum of data  that is two rows above the criteria for the sum.   I just can't seem to get it to work.

Sample data attached
EE-Book2.xlsx
0
[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 24

Accepted Solution

mankowitz earned 1000 total points
ID: 39965079
I'm not sure you want INDIRECT here which returns the values in a range when an address (i.e. "A1:A2") is specified.

Why don't you offset the second reference by two rows, like this

=SUMIF(\$F\$3:\$F\$22,"t",\$D\$1:\$D\$20)

Note that the F column starts at F3, while the D column starts at D1
0

LVL 27

Assisted Solution

ID: 39965086
Enclose the range in double quotes, i.e.
``````=SUMIF(\$F\$3:\$F\$22,"t",INDIRECT("\$E\$1:\$E\$20"))
``````
0

Author Closing Comment

ID: 39965128
Had to split the points, because both solutions worked.
0

## Featured Post

Question has a verified solution.

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

: 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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.
###### Suggested Courses
Course of the Month4 days, 23 hours left to enroll