Solved

Sum a range of cells using certain criteria in Excel

Posted on 2014-11-18
8
73 Views
Last Modified: 2014-11-19
I want to total a range of cells given certain criteria that would identify the first cell in the range to total and XX number of cells afterwards.  For instance, In the attached file I am looking to total C25 through C49.  (B7 is a user defined date and B8 is the number of cells to be summed.

Enclosure
0
Comment
Question by:Bill Golden
  • 5
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40451721
No Attachment
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40451736
Maybe this will help

=SUM(INDIRECT(ADDRESS(11+MATCH(B7,A12:A63,0),3)&":"&ADDRESS(11+MATCH(B7,A12:A63,0)+B8-1,3)))

Regards
EE2041119.xlsx
0
 
LVL 1

Author Comment

by:Bill Golden
ID: 40451750
Regardless of where I place your formula, it returns #N/A, except for cells A11..A14.  
Obviously I have lost something in translation.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40451755
Without your attachement it is difficult to know what you want
0
 
LVL 1

Author Comment

by:Bill Golden
ID: 40452091
I am sorry.  I thought I uploaded the spreadsheet with my original post.  It is attached.  Your formula appears in cell B9.
Decline-Curve-Calc.xls
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40452103
then pls try

=SUM(INDIRECT(ADDRESS(14+MATCH(B7,B15:B87,0),3)&":"&ADDRESS(14+MATCH(B7,B15:B87,0)+B8-1,3)))
Decline-Curve-CalcV1.xls
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40452135
shorter version

=SUM(OFFSET(INDIRECT(ADDRESS(14+MATCH(B7,B15:B87,0),3)),0,0,B8,1))
0
 
LVL 1

Author Closing Comment

by:Bill Golden
ID: 40454072
Excellent solution.  Thanks.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google Drive Slides: Can you set a default slide layout? 2 49
Excel Score Formula 5 67
To array or not to array 11 69
Finding a closest match in Excel 7 69
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 a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

821 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