# 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

## Files

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

## Files

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

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.

• 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

Kj, Really appreciate your help.

• 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

Yes that would be great

• 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

Yes that would be great - really appreciate your help

• 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.

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

Table 2 goes up to the 31/12/2018

• 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

No problem

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

## Files

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

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!