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.

Product	apr-14	may-14	jun-14
1a	10	50	200
1b	10	50	200
1c	10	50	200
1d	10	50	200
1e	10	50	200
1f	10	50	200
	50
	50
1g			200
1h	50		200

<tbody>
</tbody>