Sum data across sheets based on multiple criteria (row and column)

  • Hello,


    I have 22 sheets in a workbook, each with dates across the top row and metrics down column A. The metrics are things like "Revenue A, Revenue B, Expense A, Expense B" and each worksheet has the same metrics but in different orders. The dates also do not all start at the same time so are not in the same columns on each worksheet (but are in the same row, 3). I'm trying to combine them into a master sheet that adds data from each sheet based on the date and metric, so I need both a horizontal and vertical lookup, summed across sheets. Each sheet has been renamed, and I pulled the sheet names into a table named "sites" so that I can reference them using INDIRECT. "sites" is a named range, the range is all the tab names. I've tried the following formulas:


    =SUMPRODUCT(INDEX(INDIRECT("'"&sites&"'!$A$3:$G$7"),MATCH($A4,INDIRECT("'"&sites&"'!$A$3:$A$7"),0),MATCH(B$3,INDIRECT("'"&sites&"'!$A$3:$G$3"))))


    =SUMPRODUCT(SUMIFS(INDIRECT("'"&sites&"'!$A$3:$G$7"), INDIRECT("'"&sites&"'!$A$3:$G$3"), B$3, INDIRECT("'"&$sites&"'!$A$3:$A$7"), $A5))


    SUMIFS doesn't seem to work with both row and column criteria (#VALUE error). The Index match match formula seems a bit more promising - it works when I look at a single sheet, but will not sum across sheets (it gives a #VALUE error too).


    Any thoughts/advice is greatly appreciated!


    Thanks,
    B

  • Re: Sum data across sheets based on multiple criteria (row and column)


    I haven't looked at your sample workbook, but will the date of interest always occur in each worksheet even though they may not be in the same order? Or will there be situations where one or more worksheets may not contain the date of interest. If the former, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...


    =SUM(SUMIF(INDIRECT("'"&sites&"'!A3:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A3:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1)))


    Actually, there's no need to include Row 3 in the criteria range and the sum range...


    =SUM(SUMIF(INDIRECT("'"&sites&"'!A4:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A4:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1)))


    Otherwise, if the latter, try the following formula that also needs to be confirmed with CONTROL+SHIFT+ENTER...


    =SUM(IF(MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6}+1,0),{1;1;1;1;1;1;1})>0,SUMIF(INDIRECT("'"&sites&"'!A4:A7"),A4,OFFSET(INDIRECT("'"&sites&"'!A4:G7"),,MMULT(IF(N(OFFSET(INDIRECT("'"&sites&"'!A3:G3"),,{0,1,2,3,4,5,6},,1))=B3,{0,1,2,3,4,5,6},0),{1;1;1;1;1;1;1}),,1))))


    Hope this helps!


    P.S. I've assumed that your named range 'sites' defines a vertical range cells.

  • Re: Sum data across sheets based on multiple criteria (row and column)




    Thank you so much! This worked (at least on my sample dataset)! Quick Q so that I can understand this formula and apply to my full dataset.. what do {0,1,2,3,4,5,6} and {1;1;1;1;1;1} do? The example I gave had 6 columns, but the actual data set has 240 columns - will I need to adjust anything?


    Thanks!
    Bari

  • Re: Sum data across sheets based on multiple criteria (row and column)


    I'm not sure which formula you used, so replace each instance of...


    {0,1,2,3,4,5,6}


    with


    COLUMN(INDIRECT("A3:G3"))-COLUMN(INDIRECT("A3"))


    And, replace each instance of...


    {1;1;1;1;1;1;1}


    with


    TRANSPOSE(COLUMN(INDIRECT("A3:G3")))^0


    Adjust the ranges, accordingly.


    Hope this helps!


    P.S. By the way, which formula did you end up using?

  • Re: Sum data across sheets based on multiple criteria (row and column)


    Hey,
    I used the last formula because there will be instances where not all the dates are on all sheets. Those replacements worked! Thank you so much!!!! :-)
    Now I just need to figure out what everything means so I can explain this beautiful monster of a formula to my coworkers.. any chance you can say it in words in just a couple of sentences?

  • Re: Sum data across sheets based on multiple criteria (row and column)


    Quote from blw29;718146

    I used the last formula because there will be instances where not all the dates are on all sheets.


    Yeah, I thought so...


    Quote

    Those replacements worked! Thank you so much!!!! :-)


    You're very welcome!


    Quote

    ...this beautiful monster of a formula..


    That's very funny, and nicely put... lol


    Quote

    ...any chance you can say it in words in just a couple of sentences?


    Briefly, let's take a look at the SUMIF part of the formula. The first part of the formula...


    INDIRECT("'"&sites&"'!A4:A7")


    ...returns an array of ranges to be used as the criteria range for each sheet listed in 'sites'. So, let's say that the sheets listed in 'sites' are Sheet1, Sheet2, and Sheet3, we get the following array of ranges...


    Sheet1!A4:A7
    Sheet2!A4:A7
    Sheet3!A4:A7


    Then the OFFSET part of the formula is used to return an array of ranges to be used as the sum ranges for each of the sheets, where MMULT returns an array of values that specifies the column offsets corresponding to each sheet.

  • Re: Sum data across sheets based on multiple criteria (row and column)


    So I am trying to do the same thing. I do not have a "table" created. I have 10 worksheets all named differently. I am trying to "sum" data from the same date across the different sheets.....please help!

  • Re: Sum data across sheets based on multiple criteria (row and column)


    Hi,


    Please start your own thread and post your question there. Someone will likely be able to help you. However, I think you'll need to provide a bit more detail. Try posting a small sample of the data, along with the expected results.


    Cheers!


  • How will this function differ if there are text values instead of number values in the columns and the rows? E.g. if those dates are substituted with "A", "B", "C", "D", "E" and "F".

  • Hello Chéri-Lynn .... Welcome to The Forum


    This Thread is over 5 year old ...!!!


    A few suggestions :


    1. Create you own thread ...


    2. In your message, you can refer to this solution ...


    3. Attach a sample file ... with your expected result ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello Chéri-Lynn ....


    Because of the migration to the new Forum which took place yesterday (Nov.12, 2019) the thread you created has disappeared ...


    Meanwhile, had prepared an answer for you ... for the ' Text ' version of your search ...


    Attached is your Test file ... compressed as zip ... since Excel formats are currently not allowed ... :(


    Hope this will help



    Test INDIRECT 3D.zip

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().