 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hi,
This one has stumped me. I have some data as follows:
Group Code Aug-11 Sep-11
Total Sales 4 58
Total Sales 5 5
Total Sales 6 3
Total Sales 6 5
Total Sales 3 57
COG 2 8
COG 2 8
COG 1 43
COG 1 3
COG 1 37
... repeating over many months and group codes...
Which I want to display as
Group Code Aug-11
Total Sales 24
COG 7
Which is easy with SUMIF etc, however I want it to be dynamic such that if I change the heading in the display set to say another month, it will pick up that month from the underlying data. For example if I change Aug-11 to Sep-11 I want it to pick up the underlying data correctly
I think I need to be using INDEX or MATCH in conjunction with SUMIF but currently am stuck...
Thanks in advance!
in XY7:
=SUMIF($A$2:$A$4, $XX7, OFFSET($B$1, 1, MATCH($XX$6,$B$1:$D$1)-1, ROWS($A$2:$A$4), 1))
Where:
$A$2:$A$4 is your left hand column of field names
$XX7 is "Total Sales" or "COG"
$B$1 is just a fixed reference point for the offset calc assuming that your column heading dates start at B1 and go along C1, D1 etc
$XX$6 is a cell in which you change the Month you want the sumif data for
$B$1:$D$1 is your month headings array
$A$2:$A$4 is the total length of your field list down the A column
wouldn't pivot tables do this for you?