October 26, 2013
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.
June 25, 2016
October 26, 2013
July 16, 2010
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
Most Users Ever Online: 57
Currently Online: Mynda Treacy
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas