Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets). On each of these two worksheets, there are two columns – Date and Revenue. On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions. The conditions for summarising are:
1. User specifies upper and lower revenue figures – Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
2. User specifies upper and lower date limits – Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
3. User specifies upper and lower revenue figures AND User specifies upper and lower date limits – Revenue figures from both sheets lying between these figures and dates should get added as well as rows should get counted (to know instances)
The restrictions are as follows:
1. Do not want to consolidate data from two data worksheets into a single worksheet
2. Do not want to perform any ancillary calculations on the two data worksheets
If these conditions are relaxed, then alternative solution such as Pivot Tables and SUMPRODUCT() function can be used.
You may refer to my solution in this workbook.
I have solved a related problem of summarising data from multiple worksheets here.
Summarise data from multiple sheets with multiple conditions – Part II
{ 64 Comments }