+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT or SUMIF using an array for criteria

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    SUMPRODUCT or SUMIF using an array for criteria

    I'm stuck putting together a formula to lookup multiple values in another table based on more than one cell of criteria. I don't have a problem doing the sumif or sumproduct functions, but only that the criteria needs to equal more than one cell.

    For instance, this is the formula I'm using. I started with SUMIF, but then thought SUMPRODUCT might work better:
    =SUMPRODUCT(('sales worksheet'!$B$6:$B$431=CC11:CJ11)*'sales worksheet'!$W$6:$W$431))
    where it is looking up account #s in the sales worksheet that match the criteria in columns CC11:CJ11.
    Works great, but it only retrieves the first account # in columns CC11:CJ11. That is my dilemma. I've tried using HLOOKUP as the criteria, same thing. not sure what will work or maybe a different formula?

    Thanks in advance!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT or SUMIF using an array for criteria

    Try this formula

    =SUMPRODUCT(SUMIF('sales worksheet'!$B$6:$B$431,CC11:CJ11,'sales worksheet'!$W$6:$W$431))
    Audere est facere

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    Talking Re: SUMPRODUCT or SUMIF using an array for criteria

    Ah! Worked like a charm! Didn't think about using both in the same formula. Thanks again!

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: SUMPRODUCT or SUMIF using an array for criteria

    oh, so one caveat... what if the columns in row 11 can vary, such as up to 30 or more columns, e.g., DD11? is there a way for the formula to recognize the last column? If not, I'll just set it to go out a certain number of columns

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1