Consulting

Results 1 to 8 of 8

Thread: Sumif with multiple criteria?s from another worksheet

  1. #1

    Sumif with multiple criteria?s from another worksheet

    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.

  2. #2
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    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!
    SHAZAM!

  3. #3
    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!

  4. #4
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    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
    Last edited by mperrah; 09-06-2007 at 02:10 PM.

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Quote Originally Posted by mperrah
    Malcom(mdmacillop) bob (xld)have been very helpful wth sumproduct
    Mark
    They are among the best at so many things.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    Thank you Mark!

  7. #7
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    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.

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    That's pretty satisfying isn't Mark
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •