Sum Values based on header with varying column count

[Thomas Kuriakose]

Respected Sirs,

Kindly let me know how to sum the values for parameter based on specific header data and when the number of columns vary with each data.

Kindly find attached the file with details,

Thank you very much,

with regards,
thomas

 

[xlstime]

Little confused, what columns you are referring (in raw data 1 to 10)

or just wanted to sum based on Parameter if yes then refer below formula

E2 = =SUMPRODUCT((Data!$A$5:$A$15=Sum!$A2)*(Data!$C$5:$L$15))
and drag

Respected Sirs,

Kindly let me know how to sum the values for parameter based on specific header data and when the number of columns vary with each data.

Kindly find attached the file with details,

Thank you very much,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this information.

We receive this data every week for different projects and each project has varying number of units - Column C to L in this example. The next week data could be consisting of 45 columns and so on.

We need the sum of the parameters in rows with respect to the types of units (engine, rotor, controller etc.) which will be spread over these columns.

Currently we are doing this manually, going to each parameter, each type and then sum of the rows.

Kindly advise a dynamic formula which will consider the number of columns to be added.

Thanks,

with regards,
thomas

 

[bosco_yip]

Maybe………

In "SUM" Sheet C2, formula copy across to D2 and all copy down :

=SUMIFS(INDEX(Data!$5:$15,IFERROR(MATCH($A2,Data!$A$5:$A$15,0),MATCH($A2,Data!$B$5:$B$14,0)),0),Data!$2:$2,C$1)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Awesome again, thank you very much for the dynamic formula.

Sir, would like to know how to create a dynamic named range for sum in such a case, if this is a possible solution when the number of columns vary.

Thank you very much,

with regards,
thomas

 

[bosco_yip]

Respected Sir,

Awesome again, thank you very much for the dynamic formula.

Sir, would like to know how to create a dynamic named range for sum in such a case, if this is a possible solution when the number of columns vary.

Thank you very much,

with regards,
thomas

Regarding "dynamic named range", try to create a Excel Table and please press F1 in looking for further information.

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this information,

with regards,
thomas

 

[bines53]

Hi Thomas
http://forum.chandoo.org/members/thomas-kuriakose.11866/
Check, if that's okay.

=IFERROR(SUM(INDEX((C$1=Data!$C$2:$L$2)*Data!$C$5:$L$15,MATCH($A2,Data!$A$5:$A$15,0))),0)

David

 

[Somendra Misra]

Hi,

See the attached file with dynamic range, only constraint is the monthly report has to be in same format all the time, also the ColumnTotal has to be in the same way.

Regards,

 

[Naresh Yadav]

Yo can also try this one..

=IFERROR(SUMIF(Data!$C$2:$L$2,G$1,OFFSET(Data!$C$2:$L$2,MATCH($A2,Data!$A$3:$A$15,0),,)),0)

 

[Thomas Kuriakose]

Respected Sirs,

Apologies for the late reply, was travelling,

Thank you so much for the variable options provided.

Much much appreciated,

with regards,
thomas

 

[pinoy-exceler]

Here's another way to solve it (inspired by Bosco's solution)

=SUMIFS(INDIRECT("data!"&TEXT(MATCH($A2,Data!$A$1:$A$15,0),0)&":"&TEXT(MATCH($A2,Data!$A$1:$A$15,0),0)),Data!$2:$2,C$1)