Summarise data from multiple sheets with multiple conditions – Part II

{ 64 Comments }

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.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi,

    I’ve run into a couple of problems.

    B2:B12 should count number of entries based on G1:G2 dates. It calculates number of entries in B2:B92

    {=TABLE(X1,)} Y6:AD8 I can’t duplicate it. It is not listed in the name manager.

    Cell X1 highlighted in yellow. What is this used for?

    Thanks again. I’m learning

    • Hi,

      I have corrected your formula in range B7:B17 of worksheets “Stats – Ashish” in this workbook. Please check the results now.

      The Table array formula in range Y6:AD8 is the result creating a Data > Data Table. These formula have not been Ctrl+Shift+Entered. Cell X1 is also used for creating the Data Table in range Y6:AD8.

  • Hi,

    Google has not help me understand tables.

    I copied your table to AK1:AQ4
    Changed the data to
    1
    160
    For All 24 2 21 1 – –
    For other 4 – 4 – – –

    Selected cells AL3:AQ4, What if>data table>row input cell $AK$1

    It’s not working.
    AL3:AQ3 not in the table
    AL4:AL4 in table but all 4’s

    • Hi,

      After you copy and paste, where are you typing 160? Also, now that the row input cell has changed to cell AK1, you will have to change the index_number in the CHOOSE() function in column M of each worksheet to AK1 (from X1).

      Hope this helps.

  • Hi,

    160 was a mistype.
    I changed the Choose formulas. (duh)
    I’ve noticed that the Choose formulas do not always compute accurately.
    Table does not contain AM3:AQ3. AL4:AQ4 now is all –

    Thanks again for your help and patience.

    • Hi,

      What do you mean by “Choose formulas do not always compute accurately”? Upload your file to SkyDrive and share the link of the file with me. Please be descriptive when you post your question.

  • Hi,
    Trying to shorten this formula (totals number of entries in B:K) on multiple sheets First:Other
    SUMPRODUCT((First!$A$2:$A$367>=Stats!$H$1)*(First!$A$2:$A$367<=Stats!$H$2)*(First!$B$2:$K$367″”)
    +((Other!$A$2:$A$367>=Stats!$H$1)*(Other!$A$2:$A$367<=Stats!$H$2)*(Other!$B$2:$K$367″”)))

    I don’t have access to my workbook to upload it at the moment. The CHOOSE formulas would not always return the correct number of entries in all of the rows.

    Thanks for your help

    • Hi,

      It looks like you want to determine the value in cell B7 of Stats sheet without using the Data Table. Try this

      1. In cell Q2 of First sheet, enter this formula and copy down till cell Q367

      =COUNT(B2:K2)

      2. Repeat step 1 for the other sheets as well
      3. In cell B7 of Stats sheet, enter this formula

      =SUMPRODUCT((N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))>=$H$1)*(N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))<=$H$2)*(N(OFFSET(INDIRECT("'"&$P$1:$W$1&"'!Q2:Q367"),ROW(INDIRECT("1:367"))-1,,)))) Hope this helps.

    • How is this question different from the question posted earlier (which I had already answered). I had computed all values except the ones for Standard deviation. You only seem to have changed the headings in C6:G6 and C15:D15. (which were anyways not being referred to in my formulas).

      Why can’t you use my formulas which I wrote in my previous file?

  • Thank you. I did use your Sep 8th formula. I just didn’t copy it from my larger workbook to the test workbook, my oversight.
    I don’t see where I referenced C6:C6 or C15:D15

    How do I modify this formula to return the data from C2:C367

    SUMPRODUCT((N(OFFSET(INDIRECT(“‘”&$O$1:$R$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))>=$H$1)*(N(OFFSET(INDIRECT(“‘”&$O$1:$R$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))=$H$1)*(N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))=C$1)*((N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!B2:K367”),ROW(INDIRECT(“1:367”))-1,,))<=C$2)))/$B7

    Thanks again

  • Hi,

    SUMPRODUCT((N(OFFSET(INDIRECT(“‘”&$O$1:$R$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))>=$H$1)*(N(OFFSET(INDIRECT(“‘”&$O$1:$R$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))=$H$1)*(N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!A2:A367”),ROW(INDIRECT(“1:367”))-1,,))=C$1)*((N(OFFSET(INDIRECT(“‘”&$P$1:$W$1&”‘!B2:K367”),ROW(INDIRECT(“1:367”))-1,,))<=C$2)))/$B7

    Works for data in column B but not B:K

    Thanks

    • Hi,

      This is what I did to create the Data Table in range Y13:AD15.

      1. In range Z13:AD13, type numbers from 1 to 5. I have thereafter formatted the cells individually (Custom formatting) to change the display
      2. Enter formulas as shown in range Y14:Y15
      3. Select range Y13:AD15 and go to Data > Table
      4. In the row input cell, select cell AK1. Leave the column input cell blank and click on OK

      Hope this helps.

  • Hi,

    That’s what I thought too. I did manage to chart the data on a scatter chart for the 8 sheets. Tedious, but it worked, a ‘series’ for each column of data. I don’t know how to incorporate the dates H1:H2 into formulas.

  • Hi,

    Filtering the date column my other charts would update with the wrong data.

    I copied all data to another sheet (by formulas, i.e =IF(First!$B2=””,””,First!$B2)) and found a macro to filter that sheet by the dates.

    Thanks again for your help.

    PS Is it possible to use the data in two workbooks. (2013 and 2014) 1 Aug 13 – 1 Feb 14, for Stats to use in its calculation? Yes, I know one workbook would be easier, but workbooks get corrupted or crash. I know, back-up, back-up, back-up your data.

  • Hi,

    I transferred all data to one workbook. Aug – Feb charts and formulas can now calculate.

    Do I have to have a ‘what if analysis table’ for each month? Or is there an alternative method?

  • Hi,

    Each month now has an individual data table on a different worksheet. Do I need to change the formulas?

    Months are in ‘Month Stats’; Tables are in “MonthlyTables’

    SUMPRODUCT((N(OFFSET(INDIRECT(“‘”&$B$1:$I$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))>=$B$2)*(N(OFFSET(INDIRECT(“‘”&$B$1:$I$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))<=$B$3)*(N(OFFSET(INDIRECT("'"&$B$1:$I$1&"'!M2:M2923"),ROW(INDIRECT("1:2923"))-1,,))))

    Thanks again

    • Upload the file to SkyDrive and share the link of the uploaded file here. I will not spend time in rewriting any formula for you – you have to do that yourself. I will just correct for your error.

      Also, clearly mark the cells where you need my help – don’t expect me to guess your desired result.

      I have spend way too much time on helping you with this question and expect you now to solve it by yourself.

  • Hi,

    I copied the data to a new workbook, formulas were copied without the =sign then added the equal sign in the new worksheets.

    Everything was working okay. I extended the data from 1:367 to 1:2923, 40 stats tables, and was adding the next 12 months when excel started not responding again. Changing the calculation has been tried. Automatic, automatic without the data tables, and manual.

    And the monthly tables on the stats where the tables were referenced in the formulas all changed to 20% (didn’t change the tables)
    Sep-06 160 #
    All 20% 20% 20% 20% 20% 35

    Any thoughts?

  • Hi,

    The workbook is not working again. I’ve tried repairing, moving the sheets to a new workbook, different computers with excel 2007 and 2010 starter with the same results. Crashing.
    The formulas referencing the tables are the only formulas affected on the stats page. The tables sheet is not calculating all of the tables.
    Size of the workbook is 6775KB
    Any suggestions?

    • Hi,

      I cannot suggest anything unless I see the file. Please upload the file to SkyDrive and share the link of the uploaded file here. Please be very clear in pointing out what is not working. Mark those cells in yellow and give a description of the problem.

    • Hi,

      The problem is that there are way too many Data Tables and array formulas in the workbook. These are severely impacting workbook performance. Correcting for incorrect figures in the yellow coloured cells is still a secondary issue. We first have to think of a way to increase the processing speed of the workbook so that it becomes usable/editable.

    • Hi,

      So first please make the workbook usable (which may involve redesigning your workbook and/or revisiting your formulas and Data Tables) and then we I will help you correct for the numerical mistakes.

  • Hi,

    How can I modify this formula to calculate between dates in AL2 and AL3? Dates are in A2:A2923

    100*INDEX(FREQUENCY(First:Last!$B$2:$K$2923,$D$1:$D$2),2)/COUNT(First:Last!$B$2:$K$2923)

  • Hi,

    Think I have it solved except for these formulas. How do I include cells in B:K. It only calculates in cells B

    SUMPRODUCT((Other!$A$2:$A$2923>=$AL$2)*(Other!$A$2:$A$2923<=$AL$3)*(Other!$B$2:$B$2923″”))

    SUMPRODUCT((N(OFFSET(INDIRECT(“‘”&$AM$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))>=$AL$2)*(N(OFFSET(INDIRECT(“‘”&$AM$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))=$B$1)*((N(OFFSET(INDIRECT(“‘”&$AM$1&”‘!B2:B2923”),ROW(INDIRECT(“1:2923”))-1,,))<=$B$2)))/$G10

    Thanks

  • Hi,

    Formula works IF the first data point is in column B. If it is in C:K, It ignores all data points until a data point is in column B

    MPRODUCT((N(OFFSET(INDIRECT(“‘”&$AR$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))>=$AL$2)*(N(OFFSET(INDIRECT(“‘”&$AR$1&”‘!A2:A2923”),ROW(INDIRECT(“1:2923”))-1,,))=$B$1)*((N(OFFSET(INDIRECT(“‘”&$AR$1&”‘!B2:K2923”),ROW(INDIRECT(“1:2923”))-1,,))<=$B$2)))/$G9

  • Hi,

    I couldn’t solve it using two variables; min/max and dates. Went back to changing the individual formulas to match the dates.