# Thread: Summing cells (Excel 2002)

1. ## 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. ## 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. ## 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.

4. ## Re: Summing cells (Excel 2002)

=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. ## 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
•