Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

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.

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.

Related Topics:

- count the sum of a column
- splitting textpane into two columns with a line between columns
- Select Columns by the Name of Column in the Header Row
- Copying and Pasting Values From One Column To Multiple Columns Using
- Summing a range of variable size
- sum of amount deliverd group
- custom Sum function?
- SUM content of rows
- Sum colunm total in repeater
- Calculate Total Sum
- Loop, sum values and paste
- VBA code to sum
- Summing on varying number of rows in Excel using VBA
- Summing a range
- getting the sum of the last 20 cells
- Conditional Summing
- sum formula
- how to sum the even numbers only
- Sum values entered into text boxes
- Data Sum rows not hidded
- sum cells with conditional formatting color
- sum of series of natural numbers
- sum and difference of 2 bcd numbers
- Compute the sum of both positive and negative elements of an array
- Getting the sum and percentage in Excel coulmn