Solved

Using Indirect in Sumif

Posted on 2014-03-30
3
247 Views
Last Modified: 2014-03-30
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
Comment
Question by:uad
[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
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 250 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

by:MacroShadow
MacroShadow earned 250 total points
ID: 39965086
Enclose the range in double quotes, i.e.
=SUMIF($F$3:$F$22,"t",INDIRECT("$E$1:$E$20"))

Open in new window

0
 

Author Closing Comment

by:uad
ID: 39965128
Had to split the points, because both solutions worked.
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

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 how to apply various conditional formatting in Excel 2013.
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.

615 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