[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sum a range of cells using certain criteria in Excel

Posted on 2014-11-18
8
Medium Priority
?
94 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 54

Expert Comment

by:Rgonzo1971
ID: 40451721
No Attachment
0
 
LVL 54

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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 54

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 54

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 54

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Viewers will learn a basic relationship technique in Power Pivot for 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.

612 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