Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sum in columns

  Asked By: Aditi    Date: Sep 15    Category: MS Office    Views: 928
  

Here is my problem

Week 1 2 3 4 5 6 7 8 9
Month 1 1 1 1 2 2 2 2 2
Product 1 24 76 89 26 23 87 35 28 19
Product 2 34 46 89 26 23 87 35 28 19
Product 3 24 77 23 87 35 28 19 89 26
Product 4 44 26 89 26 23 87 35 28 19
Product 5 64 76 23 87 35 28 19 89 26
Product 6 44 36 89 26 23 87 35 28 19

I need a formula to sum the month total for all the products per month.
My actual spreadsheet goes the whole 52 weeks and the start date of the
year can change, so that is why i need the formula. Can anybody help

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Archer Smith     Answered On: Sep 15

I would suggest that you put in a row to total  the products  for each week and
then use Sumif() to derive the totals per month.

Working on just the sample given and assuming it starts from cell A1, put the
numbers 1-12 in A11 to A22 and put this formula  in B11 and copy it down.

=SUMIF($C$2:$K$2,A11,$C$9:$K$9)

In this formula $C$2:$K$2 is the month  numbers in row 2 (C2:K2) - for a year
the endpoint will be around BC2
A11 is the cell containing 1 for month 1
$C$9:$K$9 is the row containing the weekly totals.

 
Answer #2    Answered By: Ida Andrews     Answered On: Sep 15

This is my idea.
First, you have to define the name for each product and each month.

I copied your data and put it in my spreadsheet, then I define the
name. For example, I gave a name "Month_1" for data in cell B2:E7 and
"Month_2" for data in cell F2:J7. Also, I gave a name "Product_1" for
data in cell B2:J2, "Product_2" for data in cell B3:J3, and so on.

Second, You should put those names in one column and one row, like
this:
Month_1 Month_2
Product_1 ***
Product_2
:
:
Product_6

Then, in cell ***, you can fill it with
"=SUM(INDIRECT($A10) INDIRECT(B$9))", where A10 and B9 is the cell
which contain the "Product_1" and "Month_1" respectively. You just
need to copy the formula  into another cells.

 
Didn't find what you were looking for? Find more on sum in columns Or get search suggestion and latest updates.




Tagged: