Solved

Sum a range of cells using certain criteria in Excel

Posted on 2014-11-18
8
69 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
No Attachment
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
Regardless of where I place your formula, it returns #N/A, except for cells A11..A14.  
Obviously I have lost something in translation.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Without your attachement it is difficult to know what you want
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Bill Golden
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
Comment Utility
Excellent solution.  Thanks.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
HOW TO: Connect to the VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere (HTML5 Web) Host Client 6.5, and perform a simple configuration task of adding a new VMFS 6 datastore.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now