Sumifs to sum qty's from 3 different columns with same criteria

  • Good Morning - I hope someone can help me with a Sumifs formula I require that is completely puzzling me.


    I have attached a mock up of the output I require.


    Basically I have 2 tables - a project table (Table 1) where there are 3 phases of work - Prep, Delivery and Commissioning. All 3 phases have start and finish dates, resource and qtys attached to them. So in effect I have 3 Resource Columns, 3 Start and Finish Columns and 3 QTY Columns.


    Table 2 consists simply of a date header and the other headers consisting of the different resources. I need a formula that totals the resource requirement for the particular date in the date table - so adding together the 3 qty columns in table 1 for the particular resource and the date.


    I think the attached will probably make more sense than my attempt to describe the problem ha.


    I hope someone can help?


    Thanks


    Lee

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    This is far from elegant (I am sure a formula guru will come up with something much more elegant), but, it seems to work.


    This formula is in in C2 and dragged across and down


    =IF(SUM(IF(AND($B8>=IFERROR(VLOOKUP(C$7,$B$3:$E$5,2,0),0),$B8<=IFERROR(VLOOKUP(C$7,$B$3:$E$5,3,0),0)),IFERROR(VLOOKUP(C$7,$B$3:$E$5,4,0),""),0),IF(AND($B8>=IFERROR(VLOOKUP(C$7,$F$3:$I$5,2,0),0),$B8<=IFERROR(VLOOKUP(C$7,$F$3:$I$5,3,0),0)),IFERROR(VLOOKUP(C$7,$F$3:$I$5,4,0),""),0),IF(AND($B8>=IFERROR(VLOOKUP(C$7,$J$3:$M$5,3,0),0),$B8<=IFERROR(VLOOKUP(C$7,$J$3:$M$5,4,0),0)),IFERROR(VLOOKUP(C$7,$J$3:$M$5,2,0),""),0))=0,"",SUM(IF(AND($B8>=IFERROR(VLOOKUP(C$7,$B$3:$E$5,2,0),0),$B8<=IFERROR(VLOOKUP(C$7,$B$3:$E$5,3,0),0)),IFERROR(VLOOKUP(C$7,$B$3:$E$5,4,0),""),0),IF(AND($B8>=IFERROR(VLOOKUP(C$7,$F$3:$I$5,2,0),0),$B8<=IFERROR(VLOOKUP(C$7,$F$3:$I$5,3,0),0)),IFERROR(VLOOKUP(C$7,$F$3:$I$5,4,0),""),0),IF(AND($B8>=IFERROR(VLOOKUP(C$7,$J$3:$M$5,3,0),0),$B8<=IFERROR(VLOOKUP(C$7,$J$3:$M$5,4,0),0)),IFERROR(VLOOKUP(C$7,$J$3:$M$5,2,0),""),0)))

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Just notcied, if I add another line with the same resource (for eg another 'cleaner' in cell B4) table 2 doesn't pick this up - any further ideas?
    Thanks a lot

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    This is a bit more elegant, but still does not overcome the issue of another "Cleaner" with different dates and/or resource qty in the first table. I cannot find a solution for that scenario, hopefully somebody else will come to my rescue!


    Again put this in C8 and drag across and down


    =IF(SUM(SUMIFS($E$3:$E$5,$B$3:$B$5,C$7,$C$3:$C$5,"<="&$B8,$D$3:$D$5,">="&$B8),SUMIFS($I$3:$I$5,$F$3:$F$5,C$7,$G$3:$G$5,"<="&$B8,$H$3:$H$5,">="&$B8),SUMIFS($K$3:$K$5,$J$3:$J$5,C$7,$L$3:$L$5,"<="&$B8,$M$3:$M$5,">="&$B8))=0,"",(SUM(SUMIFS($E$3:$E$5,$B$3:$B$5,C$7,$C$3:$C$5,"<="&$B8,$D$3:$D$5,">="&$B8),SUMIFS($I$3:$I$5,$F$3:$F$5,C$7,$G$3:$G$5,"<="&$B8,$H$3:$H$5,">="&$B8),SUMIFS($K$3:$K$5,$J$3:$J$5,C$7,$L$3:$L$5,"<="&$B8,$M$3:$M$5,">="&$B8))))

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Both the formulas are twice as long as they need be because you want a blank cell when the SUM function returns 0.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Thanks again for that, to be honest I dont mind if they return zero......I defintely need to find the addtional lines solution though..........Can anyone else help??


    Thanks

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    To have the zero entries the formula would be


    =SUM(SUMIFS($E$3:$E$5,$B$3:$B$5,C$7,$C$3:$C$5,"="&$B8,$D$3:$D$5,">="&$B8),SUMIFS($I$3:$I$5,$F$3:$F$5,C$7,$G$3:$G$5,="&$B8,$H$3:$H$5,">="&$B8),SUMIFS($K$3:$K$5,$J$3:$J$5,C$7,$L$3:$L$5,"="&$B8,$M$3:$M$5,">="&$B8))


    I am still trying to get a solution for the issue.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Would a UDF be acceptable? I could write one that would give the required result for all scenarios.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Ok, one more question. Will each of the 3 "Table 1" tables have a maximum of 3 rows of data, or could any one of them be more than that at any time?


    I assume "Table 2" could be any number of rows of dates.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    OK, in that case I will move Table 2 from below Table 1 to the right of Table 1, is that OK?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    Try the attached. I have made 3 Dynamic Named Ranges for the data in the three sections of Table 1, you can add, remove or edit data in each section without needing to change anything in the function or anything in the "Refers To" property of the named ranges.


    In a standard module


    Then in Cell O3, copied across and down


    =ResourceCounts($O3,P$2)

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    At first view this looks absolutely fantastic and exactly what I need. I will take some time to work with it and understand the code also.
    Thank you so much for taking the time to do this.
    Much appreciated indeed.

  • Re: Sumifs to sum qty's from 3 different columns with same criteria


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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