SUM a range of sales based on month

[Manos]

Dear All

I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month

For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on

Any good ideas?

Thanks in advance Manos

 

[Guest]

One way ..

Assuming data in row2 down
and in col A are the month numbers: 1, 2,3, 4, etc

Put in AK2: =IF(A2="","",SUM(OFFSET($X2,,,,A2)))
Copy down as required

 

[Manos]

I have the month always in A1
i change it manualy
in Column A from A5 till A1500 i have data

 

[Guest]

I have the month always in A1
i change it manually
in Column A from A5 till A1500 i have data

Put in AK5: =IF($A$1="","",SUM(OFFSET($X5,,,,$A$1)))
Copy AK5 down to AK1500

---

 

[Don Guillett]

You say you have a product code and a month in col A. How?
1-222222c
222222c-1
or ?

 

[Manos]

A1 has the month, which i change it manualy

and form A2 - A1500 has product names and codes

 

[Guest]

Implemented in OP's sample file (sent over)
---------------------------
Put in Q6: =IF($F$1="","",SUM(OFFSET($D6,,,,$F$1)))
Copy down

Adapt to suit your continental Excel ..
(replace commas [separator] with semicolons)

---

 

[Flintstone]

This works to sum columns per month based on the date in cell A1.

=SUM(INDIRECT("A5:"&CHAR(MONTH(A1)+64)&65536))

It might help you out.

 

[Manos]

Thank you MAX
It works perfectly

A1 has the month, which i change it manualy

and form A2 - A1500 has product names and codes

 

[Guest]

Thank you MAX
It works perfectly

Glad to hear that !
Thanks for the feedback ..

---