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

Freddyfroo · Jul 5th 2017

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

**KjBox** · Jul 5th 2017

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)))

Freddyfroo · Jul 5th 2017

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

KjBox, Wow, I think that's literally the longest formula I have ever come across - that being said it does work so thanks ever so much for that.

Freddyfroo · Jul 5th 2017

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

**KjBox** · Jul 5th 2017

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))))

**KjBox** · Jul 5th 2017

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.

Freddyfroo · Jul 5th 2017

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

**KjBox** · Jul 5th 2017

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.

Freddyfroo · Jul 5th 2017

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

Kj, Really appreciate your help.

**KjBox** · Jul 5th 2017

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.

Freddyfroo · Jul 5th 2017

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

Yes that would be great

**KjBox** · Jul 5th 2017

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.

Freddyfroo · Jul 5th 2017

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

Yes that would be great - really appreciate your help

Freddyfroo · Jul 5th 2017

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

No, table 1 could have possibly hundreds of rows, this will need to be dynamic.

Freddyfroo · Jul 5th 2017

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

Table 2 goes up to the 31/12/2018

**KjBox** · Jul 5th 2017

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?

Freddyfroo · Jul 5th 2017

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

No problem

**KjBox** · Jul 5th 2017

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

Code

Function ResourceCounts(r As Range, r2 As Range) As Integer
    Dim x, y, z, i As Long, sResc As String, Dt As Date
    x = [PreperationLU]: y = [DeliveryLU]: z = [CommisioningLU]
    Dt = CDate(r): sResc = r2
    
    Application.Volatile
    For i = 1 To UBound(x, 1)
        If x(i, 1) = sResc And CDate(x(i, 2)) <= Dt And CDate(x(i, 3)) >= Dt Then
            ResourceCounts = ResourceCounts + CInt(x(i, 4))
        End If
    Next
    For i = 1 To UBound(y, 1)
        If y(i, 1) = sResc And CDate(y(i, 2)) <= Dt And CDate(y(i, 3)) >= Dt Then
            ResourceCounts = ResourceCounts + CInt(y(i, 4))
        End If
    Next
    For i = 1 To UBound(z, 1)
        If z(i, 1) = sResc And CDate(z(i, 3)) <= Dt And CDate(z(i, 4)) >= Dt Then
            ResourceCounts = ResourceCounts + CInt(z(i, 2))
        End If
    Next
                
    
End Function

Display More

Then in Cell O3, copied across and down

=ResourceCounts($O3,P$2)

Freddyfroo · Jul 5th 2017

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.

**KjBox** · Jul 5th 2017

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

You're welcome.

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

Participate now!

Share