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

x
?
Solved

Sum a range of cells using certain criteria in Excel

Posted on 2014-11-18
8
Medium Priority
?
88 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
[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
  • 5
  • 3
8 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40451721
No Attachment
0
 
LVL 52

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
Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

 
LVL 52

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 52

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 52

Accepted Solution

by:
Rgonzo1971 earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 the basics of using filtering and sorting in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

715 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