Sum if - More than one column, mivng range, fix starting point

**Skoum** · 11-13-2012, 06:53 AM

Hi,

I have the following Database :

Columns :
A : Store Number
B : Jan
C: Feb
D : Mar
Etc..

Rows :
A1 : Store 1
A2 : Store 2
A3 : Store 1
A4 : Store 1
A5 : Store 2
B1 to DXX : Data
Etc.

I would like to automatize the report in a way that when I update the month (in cell X10 for instance), the sumif formula will update its range automatically.

Example (to retrieve store 1 data) :
* If cell X10 is set on Jan, the formula should be : sumif(A:A,"store 1",B:B)
* If cell X10 is set on Feb, the formula should be : sumif(A:A,"store 1",B:C)
* If cell X10 is set on March, the formula should be : sumif(A:A,"store 1",B:D)

How can I automatize this?

**TMS** · 11-13-2012, 07:33 AM

This is one way to adjust the column as you have described:

=SUMIF(A:A,"store 1",INDIRECT(CHOOSE(MATCH(X10,{"Jan","Feb","Mar"},0),"B","C","D") & ":" & CHOOSE(MATCH(X10,{"Jan","Feb","Mar"},0),"B","C","D")))

However, I'm not sure you can get SUMIF to sum more than one column.

Anyway, it's a technique to play with.

Regards, TMS

Edit: I started with:

=SUMIF(A:A,"store 1",INDIRECT("B:" & CHOOSE(MATCH(X10,{"Jan","Feb","Mar"},0),"B","C","D")))

but that always summed column B

**Deventus** · 11-13-2012, 07:46 AM

Hi

Would using a pivot table not prove more useful, and far quicker. I use a lot of sales data for regions / stores etc by week and month and create preset pivot templates.
if you have the data file as above, and then a pivot table, which references the full database, when you enter new data in, refresh the PT and its all ready to go.
You can use a the report filter tab at the top to select stores, or months,
Adi

**Skoum** · 11-13-2012, 09:00 AM

Hi,

Thanks both for you reply.

I cant use pivot table is this file, that would make it to big in size. The database is a retrieve from hyperion (Actual, Budget, Last year, local currency, EUR), I have some mapping tables in between etc. Wont go too much in to details but the pivot table is not a good option, I need formulas.

Indeed the sumif does not work......... is there any other formula that could be used here ? Not sure if sumproduct could work...?

Thanks in advance for your help

**TMS** · 11-13-2012, 09:35 AM

You could add a helper column and use the technique described above to adjust the columns included in a simple SUM for each row.

You could then use your SUMIF to produce subtotals by store using the helper column.

Regards, TMS

**Skoum** · 11-13-2012, 10:14 AM

Hi TMS,
Thanks for your answer.

Not sure what you mean by a helper column but i don't want to pollute the report with extra column that I would need to update manually every month if a formula can do the job.

English is not my primary language so maybe I havent been clear enough, I have included a simple example of I would like to achieve.
I don't think this should be to complicated?

example.xlsx