Sum Area Based on Compound Row and Column Criteria

  • My question is this: Is it possible to add the values in a large dataset based on multiple row and multiple column criteria? Does anyone dare face the maw of madness and explain how to have 3 or 4 criteria (1-2 rows and 2 columns) in a single formula? I am confident it can be done, but I have been at it for hours and I hit a wall. Any working sample would be welcome. I've not had any luck finding something quite like this online. Here's a brief overview of the situation. All my formulas are on the "output" workbook listed below. [INDENT]
    Variables: [Core.xlsm]Parameters!$A$1:$Z$100
    Dataset: [Core.xlsm]Data!$A$16:$AR$5320
    Output: [Reports.xlsx]Summary!$A$20:$L$50


    Row Criteria 1: [Reports.xlsx]Summary!B$22
    Row Criteria 2: =RIGHT(B$21,LEN(B21)-FIND(" ",B21))


    Column Criteria 1: [Reports.xlsx]Summary!$A23
    Column Criteria 2: <User Selected Reference or Value>[/INDENT]

    Result:


    I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include:


    Code
    =SUMIFS([Core.xlsm]Data!$J$16:$K$5320,[Core.xlsm]Data!$BD$16:$BD$5320,D$22,[Core.xlsm]Data!$O$16:$O$5320,RIGHT(B$21,LEN(B21)-FIND(" ",B21)))


    I can process any combination of one row and one column criteria to return a conditional sum on a single column of values using this formula:


    Code
    {=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23),([Core.xlsm]Data!$J$16:$AR$5320))}


    But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth:


    Code
    {=SUMPRODUCT(([Core.xlsm]Data!$BD$16:$BD$5320=B$22)*([Core.xlsm]Data!$J$15:$AR$15=$A23)*([Core.xlsm]Data!$O$15:$O$5320=RIGHT(B21,LEN(B21)-FIND(" ",B21))),([Core.xlsm]Data!$J$16:$AR$5320))}


    My dataset has unique column names but the data itself is mostly calculations based on Indirect and Index Match references to the detail worksheets where the values of each line item in the dataset sheet are calculated based on their unique values combined with the parameters from the variables sheet. There are thousands of sheets, one for each row in the dataset. Yes the workbook is huge. Yes I'd rather use SQL, but it is not an option for this project.


    I have attached a sample workbook but in the process of whittling it down to a small enough dataset, I realize that the formulas I listed here may not match exactly anymore. Thanks for perusing my dilemma.

  • It would help us tremendously if your sample formulas would match the actual data you posted. Your ranges are of different sizes and don't seem to reference the same data that is your workbook.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • It would help us tremendously if your sample formulas would match the actual data you posted. Your ranges are of different sizes and don't seem to reference the same data that is your workbook.


    My apologies. I assure you I updated them inside the SUMMARY sheet in the sample workbook, but I forgot to update them in the code blocks in my post. I'm trying to figure out how to edit my original post to fix them, but so far no luck.

  • Bottom right corner of your post there is an "edit" icon. Click it and make your changes, then click Save Changes.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Hmm. I see an Edit on your first post... but that could be because I have Moderator priviliges...


    Just reply to this post and add an attachment (paper w/clip icon at top right of reply box)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Hmm. I see an Edit on your first post... but that could be because I have Moderator priviliges...


    Just reply to this post and add an attachment (paper w/clip icon at top right of reply box)


    That I can do. So I made a few updates in the sample workbook. I want to emphasize that all the formulas I have created and need to create will be in the SUMMARY tab. Here's what my original post would look like had I been able to edit it:



    My question is this: Is it possible to add the values in a large dataset based on multiple row and multiple column criteria? Does anyone dare face the maw of madness and explain how to have 3 or 4 criteria (2 rows and 2 columns) in a single formula? Here's a brief overview of the situation. All my formulas are on the "summary" worksheet.


    Variables: [Core.xlsm]Parameters!$A$1:$O$30 <----(This sheet was cleaned for the sample and is not used)
    Dataset: [Core.xlsm]Data!$A$1:$BD$100 <----(Source data my formulas reference, this is a minuscule sample population, I don't expect all criteria to produce matches)
    Output: [Reports.xlsx]Summary!$A$1:$BD$13 <----(Active worksheet for this request, all variables are pulled from the column and row headings)


    Criteria 1: [Reports.xlsx]Summary!C$2 <----(Based on the column header value)
    Criteria 2: =RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)) <----(This extracts the number from the title "Phase #")
    Criteria 3: [Reports.xlsx]Summary!$A6 <----(based on the primary row header value "Project Name")
    Criteria 4: [Reports.xlsx]Summary!$B6 <----(based on the secondary row header value "Region")
    Criteria 5: [Reports.xlsx]Summary!$B1 <----(based on the "Key" value applied by the user, if they wish to provide one)



    Result:


    I can use the SUMIFS function to return matches on any multiples row criteria OR multiple column criteria I wish to include scuh as this formula in Summary!C6:


    Code
    =SUMIFS(Data!$R$2:$R$100,Data!$BD$2:$BD$100,C$2,Data!$AT$2:$AT$100,RIGHT(C$1,LEN(C$1)-FIND(" ",C$1)))


    I can process any combination of one row and one column criteria to return a conditional sum on a single column of values using this formula in Summary!C3:

    Code
    {=SUMPRODUCT((Data!$BD$2:$BD$100=C$2)*(Data!$J$1:$AR$1=$A3),(Data!$J$2:$AR$100))}


    But this breaks down and returns an #N/A error when I try to add in a third criteria, let alone a fourth or fifth (prospective formula for C4 if it worked):

    Code
    {=SUMPRODUCT((Data!$BD$2:$BD$100=B$4)*(Data!$A$1:$AR$1=A$4)*(Data!$O$2:$O$100=RIGHT(C1,LEN(C1)-FIND(" ",C1))),(Data!$J$2:$AR$100))}

    I have attached a sanitized sample workbook that is 100 records (from a source set of over 5000). All candidate formulas should appear in Summary!C3:C13 so they can be copied into the columns to the right.

  • Before starting I noticed there is a "Key" designation in 'Summary' B1. I saw no indications in examples that you wish to include those (from column 'Data' BG). If you do you will need to include additional criteria. Hopefully the enclosed can serve as a guide on how to do that.


    The formula filled down and across

    Code
    =IFERROR(SUMPRODUCT(INDEX(Data!$A$2:$BG$100,,MATCH(Summary!$A3,Data!$A$1:$BG$1,0))*
    (INDEX(Data!$A$2:$BG$100,,MATCH($B$2,Data!$A$1:$BG$1,0))=$B3)*(INDEX(Data!$A$2:$BG$100,,MATCH("Type",Data!$A$1:$BG$1,0))=C$2)*
    (INDEX(Data!$A$2:$BG$100,,MATCH("Phases",Data!$A$1:$BG$1,0))=LOOKUP(1E+306,--RIGHT(LOOKUP("zzzz",$C$1:C$1),ROW($1:$10))))),0)


    There are merged cells in 'Summary,' and the Phases numbers in 'Data' suggest there could be multiple digit phase #s. If you are not aware of it merged cells cause havoc for formulas. In consideration of both find this portion of the formula that extracts Phase #s. It is reliable but a bit involved. If you would like help analyzing it please let us know.

    Code
    LOOKUP(1E+306,--RIGHT(LOOKUP("zzzz",$C$1:C$1),ROW($1:$10)))

    .

  • This alternative might simpler to follow on the Phase #s extraction part.

    Code
    =IFERROR(SUMPRODUCT(INDEX(Data!$A$2:$BG$100,,MATCH(Summary!$A3,Data!$A$1:$BG$1,0))*
    (INDEX(Data!$A$2:$BG$100,,MATCH($B$2,Data!$A$1:$BG$1,0))=$B3)*(INDEX(Data!$A$2:$BG$100,,MATCH("Type",Data!$A$1:$BG$1,0))=C$2)*
    (INDEX(Data!$A$2:$BG$100,,MATCH("Phases",Data!$A$1:$BG$1,0))=[COLOR=#FF0000][B]--TRIM(RIGHT(SUBSTITUTE(LOOKUP("zzzzz",$C$1:C$1)," ",REPT(" ",99)),99))[/B][/COLOR])),0)

  • It required some reverse engineering but I have adapted your code to work! Thank you so much.


    The only dilemma is trying to conditionally exclude variables. I tried using an IFERROR(IF((blah blah)) around the constituent parts of the SUMPRODUCT but it does not work. I suspect this may because some of my data resolves to blanks or spaces, which seem to resolve to #VALUE errors and trigger the IFERROR encapsulating the entire formula.


    Any thoughts about how to work around this? I'm trying to just clean the source data but it in turn is entirely formulas referencing other sheets and is proving to be quite an undertaking.

  • Sorry, I have been gone a while, and had lost sight of this thread. My bad. :(


    It sounds like the condition you speak of is IFERROR(long formula,""). That would trigger errors. Try IFERROR(long formula,[SIZE=14px]0[/SIZE])

  • Sorry, I have been gone a while, and had lost sight of this thread. My bad. :(
    It sounds like the condition you speak of is IFERROR(long formula,""). That would trigger errors. Try IFERROR(long formula,[SIZE=14px]0[/SIZE])


    Flame, better late than never. I appreciate the follow-up. I did eventually figure out that I either needed to use zeros instead of spaces or use a double unary to ensure I was returning a numeric value and not "True/False" in some of the nested function calls to other formulas. It is actually opportune that you made your presence known, granted I saw this a few months later.


    I have been successfully using your code for months, tailoring it as required to meet the needs of various situation. But yesterday and today I have been beating my head against a wall trying to get it to work. I keep dropping it and coming back to it, and maybe it's the 28 hours with no sleep, but I just can't seem to get it right. Today of all days I could really use some expertise.


    I have some columns labeled as periods (YYYYMM) but no matter what I do, I cannot get this methodology to register a result and return the desired values. It just returns nothing. I have tried formatting it as Text, Numeric, and Custom Date formats; I've tried regular formulas and array formulas, but nothing seems to work. See Summary worksheet starting at row 15 and the Data worksheet starting at column CJ. Any suggestions?

  • Hello,


    In your sheet ' Data ' ... cell CJ1 ... you should have the following Actual date formula ... :wink:


    Code
    =DATE(2019,1,1)


    Hope this will help

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


  • Thanks Carim, I ended up doing manual paste values to work around the problem, so I will definitely give this a try when I have a moment and let you know how it goes.

  • No problem ... :wink:


    Feel free to come back to the Forum whenever needed ...

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!