Thank you for your reply to Creteria # 195756
But the value should change automatically in cell C5, when we selecting the numbers from drop down list in working all tab cell B2.
Cell B2 contain drop down list, when we select the 1 , it should sum the values from data sheet in column C with reference of B1 and B5 in Working All tab.
When we select 2 in cell B2 , it should sum the values from data sheet in column C and column D with reference of B1 and B5 in Working All tab.
if its 3, it should sum the values from data sheet in column C, column D and Column E with reference of B1 and B5 in Working All tab.
We tried with if and sum ifs formula, is there any other simplest formula to find the values based on the above.
Thank you in advance for your kind support.
Hi Revathi
No idea what you meant by "simplest" formula.
For me all formulas are complicated.
See if the attachment helps.
Sunny
Thank you Sunny,
formulas are complicated, but mine is too big and too complicated right.
Just curious to know is there any other option or any other alternate method..
Hi Revathi,
The reason your formulas are too big and complicated is because your data in not in the correct layout, for if it were then you could use a PivotTable to summarise this data.
See the file attached - sheet 'Unpivoted Data'. I used Power Query to Unpivot your data, then a PivotTable to summarise it they way you want. I also included a Slicer (available in Excel 2010 onwards) to allow you to select the quarters you want to summarise.
More reading on the correct format for your data here: https://www.myonlinetraininghub.com/excel-tabular-data-format
Mynda
