Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

Sum a range of cells using certain criteria in Excel

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
Bill Golden
Asked:
Bill Golden
  • 5
  • 3
1 Solution
 
Rgonzo1971Commented:
No Attachment
0
 
Rgonzo1971Commented:
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
 
Bill GoldenExecutive Managing MemberAuthor Commented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
Without your attachement it is difficult to know what you want
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
Rgonzo1971Commented:
shorter version

=SUM(OFFSET(INDIRECT(ADDRESS(14+MATCH(B7,B15:B87,0),3)),0,0,B8,1))
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Excellent solution.  Thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now