Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Location
    Denver, CO
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing cells (Excel 2002)

    I have what is probably a simple problem. Let's say I have sales data for 52 weeks of the year. Cells A1 through AZ 1 are numbered 1 to 52 and cell BA1 is labeled "Total." After each week ends, I'll enter the sales amount in the corresponding cells in row B. Week 1 is entered in cell B1, etc. and the current running total will be in cell BA2. So, arriving at a total is easy.
    However, there will be times I want to know only weeks 1 through 20, for example, or maybe just the weeks in Quarter 2. So somehow I will need to tell a formula or function the begin and end weeks for my desired total. Also, the workbooks will contain hundreds of rows, not just the one (row [img]/forums/images/smilies/cool.gif[/img] in my example, that will need the same total, i.e. at some point, I may want every row to total weeks 5-8, or 6-20, for example.
    Thanks for any help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Summing cells (Excel 2002)

    Put the beginning week in one cell, say BC1, and the ending week in another, say BC2. In cell BA2, enter this formula:

    =SUM(($A$1:$AZ$1>=$BC$1)*($A$1:$AZ$1<=$BC$2)*A2:AZ 2)

    It is an array formula; it must be confirmed with Ctrl+Shift+Enter instead of just Enter. Next, fill down this formula to BA3 etc., as far as needed.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Summing cells (Excel 2002)

    =SUMIF(match_range, criteria,sum_data) is most likely to be what you are looking for. When you want a from/to range, use two SUMIFs in the form:

    =SUMIF(weeknumber_range,"<=20",data_range)-SUMIF(weeknumber_range,"<5",data_range)

    You can make this more fexible by putting the criteria in separate cells, in this example D1 & D2, as follows:

    =SUMIF(weeknumber_range,"<="&D1,data_range)-SUMIF(weeknumber_range,"<"&D2,data_range)

    For some more on SUMIF see <!post=this thread,433093>this thread<!/post>, and search for others within this Forum.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing cells (Excel 2002)

    How about,

    =SUMPRODUCT(--(A1:AZ1>=BB1),--(A1:AZ1<=BB2),A2:AZ2)

    Where BB1 and BB2 house week numbers of interest. If summing more than one row,

    =SUMPRODUCT((A1:AZ1>=BB1)*(A1:AZ1<=BB2)*(A2:AZ4)

  5. #5
    New Lounger
    Join Date
    Dec 2000
    Location
    Denver, CO
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing cells (Excel 2002)

    Thanks, guys! Now I have three ways to accomplish the task. I appreciate the help.
    Happy Holidays, Rick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •