Help with Formulas for calculating values from Table

[ajinkya deshmukh]

Dear All,

We need some help for calculating data values from extract available from SAS tool used by us.

We have attached an excel file which consists 2 sheets -

Sheet 1 - Summary
Sheet 2 - Data

What we want is that the numbers in the sheet "Data" highlighted in Red is available against the corresponding parameter in Sheet "Summary" .

In sheet "Summary" in few lines we have put the value which needs to come from sheet "Data".

Appreciate your response on this.

Regards
Ajinkya

 

[PeterB]

Hi ajinkya,

This is a more difficult problem because of the way the data is structured. It looks like a pivot table (with subtotals already included). It will be much easier to solve this problem if you can export your data so it looks like a table (without gaps or subtotals). This would enable you to use a pivot table to quickly and easily get the subtotals your require.

I have attached an updated workbook showing an example of the principle.

Failing that, you could use a horrible formula like:

=SUMIF(Data!A:A,Summary!$A2,Data!$H:$H)+SUMIF(Data!B:B,Summary!$A2,Data!$H:$H)+SUMIF(Data!C:C,Summary!$A2,Data!$H:$H)+SUMIF(Data!D:D,Summary!$A2,Data!$H:$H)+SUMIF(Data!E:E,Summary!$A2,Data!$H:$H)+SUMIF(Data!F:F,Summary!$A2,Data!$H:$H)+SUMIF(Data!G:G,Summary!$A2,Data!$H:$H)

Which will do the job but will also consume vast amounts of memory / processing power if it is copied over many rows.

Thanks,

Peter

 

[Somendra Misra]

@ajinkya deshmukh

Please have a look of attached file. Check the results are OK against each entry of not?

Just advise if any issue?

Regards,

 

[NARAYANK991]

Hi Ajinkya ,

If VBA is an option , it becomes easier , since using VBA we can identify the colored cell(s).

Narayan