SUMIF Help - selecting a specific column from a range of columns

[SamWaters]

Hi All, I'm struggling with this one - any help would be much appreciated & it's probably really simple.


Firstly, we have a data source which resembles something like:

Col A Col B Col C ... Col M
Product Apr-14 May-14 ... Mar-15
1a
1b
1c
1d
etc

On a separate worksheet used to summarise, we have a drop down selector, whereby you select which month you'd like to view (Apr-14 to Mar-15).

How do I link it so that the summary picks up and sums up the month the drop down (e.g. in cell A1) says.

I've tried a sumif, using a sum range which spans col B to M, and referenced the drop down cell in the criteria, but it didn't work.

 

[StefanVL]

Try this formula:


=SUM(OFFSET(B1:M1;1;MATCH(H2;B1:M1;0)-1;COUNTA($A$2:$A$5);1))

H2 = dropdown list
A2:A5 = list of products


Do note that there shouldn't be any blanks in the list of the products.

 

[StefanVL]

This should be a better one, it doesn't matter if there are blanks:

=SUM(OFFSET(B1:M1;1;MATCH(H2;B1:M1;0)-1;MATCH(REPT("z";99);A4:A5;1);1))

 

[StefanVL]

Found a small defect in the formula which adds numbers when there aren't any products linked to it.
Entering the following formula should avoid this problem:

=SUM(IF(A2:A11<>"";OFFSET(A2:A11;0;MATCH(H2;B1:M1;0);MATCH(REPT("z";99);A2:A11);1);0))

enter with ctrl+shift+enter

see below for my used table, answer for apr-14 should be 110 instead of 210.

[TABLE="width: 212"]
<tbody>[TR]
[TD="width: 53"]Product[/TD]
[TD="class: xl63, width: 53, align: right"]apr-14[/TD]
[TD="class: xl63, width: 53, align: right"]may-14[/TD]
[TD="class: xl63, width: 53, align: right"]jun-14[/TD]
[/TR]
[TR]
[TD]1a[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1b[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1c[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1d[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1e[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1f[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1g[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]1h[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]