Sumif with multiple criteria?s from another worksheet

[*Chi*]

I'm trying to do a sum if calculation that will sum a column from another worksheet (Billing) given 2 criterias and put the result on the other worksheet (Budget). So, if column E (Sub-Category) equals "2B. Destructive Testing: Preparation/Attendance" AND if column M (Assoc.) equals "BK", Then Sum Column L (Hours) for those corresponding rows.

I've tried severeal formulas but I cant figure out why it doesnt work:
=IF(Billing!$M:$M="BK",SUM(VLOOKUP("2B. Destructive Testing: Preparation/Attendance",Billing!$E$9:$M$600,8,FALSE),0))
and
=IF(AND(Billing!M:M="BK",Billing!$E:$E="2B. Destructive Testing: Preparation/Attendance"),SUM(Billing!$L:$L))

Please see attached workbook.

[Shazam]

Input formula in cell D3 and copy down.

=SUMPRODUCT((LEFT(Billing!$E$10:$E$1000,3)=2&A3)*(Billing!$M$10:$M$1000="BK"),Billing!$L$10:$L$1000)

Hope it helps!

[*Chi*]

Thank you Shazam! The formula worked!!!
I understand most of the formula but, would you mind explaining the formula to me so I may understand it for future reference? Such as why use sumproduct instead of sumif and what does the =2 mean?
Again, your quick response is much appreciated!

[mperrah]

Take a look at this
http://www.contextures.com/xlFunctions01.html
search down to sumproduct
I have anopther link I'll post when I dig it up.
Malcom(mdmacillop) bob (xld)have been very helpful wth sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Mark

[lucas]

Malcom(mdmacillop) bob (xld)have been very helpful wth sumproduct
Mark
They are among the best at so many things.

[*Chi*]

Thank you Mark!

[mperrah]

For once I had help to offer.
I usually am asking for help,
but the sumproduct I have been using for a while now thanks to this forum.
Be sure to read all the details.
The biggest thing to note is all the ranges of data you are scanning need to be the same length
ie. using named ranges of columns,
the ranges named have to include the same number of rows, or it will error.

[lucas]

That's pretty satisfying isn't Mark