SUMIF with changing column headers.

[Jim McEwan]

Hi all,

Basically I am looking for a cross between a [SUMIF] and a [VLOOKUP/MATCH]. I have one table of data: TABLE A; it contains values from a month against place format. I have attached a simplified version for you to take a look at.

What I need is to sum all the relevant values into TABLE B.

Although this is simple enough using [SUMIF], my problem arises because the column titles in TABLE A are not always in the same place, they change position everytime the report is run.

Can someone please help??

DATA-EXAMPLE.xlsx

[ragulduy]

Try something like:
=SUM(($A3:$A17=$I2)*IF($B$2:$G$2=I$3,$B$3:$G$17))
as an array formula in I4 (confirm with ctrl + shift + enteR) and copy down/across as neccessary.

[martindwilson]

in i4 filled across
=SUMIF($A$3:$A$17,$I2,INDEX($B$3:$G$17,0,MATCH(I3,$B$2:$G$2,0)))
copy to i10 and i16

[newdoverman]

I used sumproduct for this solution:

[Jim McEwan]

@martinwilson

Hi Martin,

That worked perfectly for me thanks, but now I have to ask for something a little more complicated. So I also want to return the values depending on the weeks of the month too, I have updated the spreadsheet with the new requirements. So if I change the week on TABLE B then it will return the appropriate values.

Thanks for your help so far.

Regards,

JM

DATA-EXAMPLE PART(2).xlsx

[martindwilson]

id go for the sum product version in j4
=SUMPRODUCT(($B$3:$B$17=$K2)*($A$3:$A$17=$J2)*($C$2:$H$2=J3)*($C$3:$H$17))

[newdoverman]

I have produced a workbook using SUMPRODUCT to include the week requirement.