+ Reply to Thread
Results 1 to 9 of 9

SUM PRODUCT QUERY to lookup a query based on item type and item price range

  1. #1
    Registered User
    Join Date
    08-05-2010
    Location
    midlands
    MS-Off Ver
    Excel 2003
    Posts
    16

    Question SUM PRODUCT QUERY to lookup a query based on item type and item price range

    HI folks,
    I have a sheet, where I need it to do calculation 2 functions.
    As a simple example, I have 5 items for sale, which have a relative price if 1-10 items are sold. If 11-20 items are sold the price will be different, 21-30 items the price will be different...and so on...

    So I have two variables.
    The items.
    The price bands.

    I have attached a basis example file, so hopefully someone can help me..

    I didn't want to use if statements or arrays, as I have many items and many price bands.
    I have found a sum product that works:
    =SUMPRODUCT((E1:U1<=B22)*(E2:U2>B22)*E6:U6)

    However the problem is that the end part (F6:U6) is not dynamic. For each product i need to adjust the formula to change a row. This will work, but if a new item is added, then the formula will need to be manually adjusted.
    After spending hours looking, i have found INDRECT function, where i could relate to the row, e.g.
    =SUMPRODUCT((E5:U5<=B26)*(E6:U6>B26)*INDIRECT("E"&C6&":"&"E"&C6)) but that doesn't seem to work. If it did reference the row number, then this could be dynamic, regardless if the list was added to or sorted.


    It may be that sumproduct is wrong, and there maybe a much simpler way, but after days of google research I still can't find!!

    Many thanks in advance

    Simon
    Attached Files Attached Files
    Last edited by stwigge; 08-18-2010 at 03:11 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    Where are you stipulating the product (other than by altering row reference) ?

    It's not really clear what you want do (to me at least) though what I would add is that:

    =SUMPRODUCT((E1:U1<=B22)*(E2:U2>B22)*E6:U6)

    can be replaced by a far more efficient alternative namely:

    =INDEX($E6:$U6,MATCH($B$22,$E$1:$U$1))

    however, I don't think that's really getting to the heart of the problem.

    Perhaps you could elaborate by posting a few expected results - you mention product in plural etc...

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    Hi,
    attached an example file with a possible solution.
    You can adapt the cell where you want to enter the item to be searched.

    I provided for a dynamic range for the item names ( if you add some the range will adapt), but leave it up to you to define the other dynamic ranges ( see http://www.contextures.com/xlNames01.html#Dynamic) ( sorry I have to go)
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-05-2010
    Location
    midlands
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    First of all thanks for the prompt reply, and secondly I'm sorry if it's confusing.

    I'v added another tab now called 'Summary' to show what I need to do.

    The formula in D6 works, however it is not dynamic.
    I.e. If i change the drop down in cell A6 to another item, the result in D6 will not be relevant

    Hope this makes sense now?


    Simon
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    see arthurbr's prior post - the attachment offers an item specific example [D32] ie where item is variable

  6. #6
    Registered User
    Join Date
    08-05-2010
    Location
    midlands
    MS-Off Ver
    Excel 2003
    Posts
    16

    Smile Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    Sorry Arthurbr

    I had already sent my last post before your answer had come through.


    That's amazing!


    After first trial it seems perfect, just what I wanted.

    Great help

  7. #7
    Registered User
    Join Date
    08-05-2010
    Location
    midlands
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    Hi ArthurB

    i'm really sorry but just had a bit of trouble..

    reason being, I'm now trying to adapt it to my 'actual' worksheet (unfortunately I couldn't send you the spreadsheet for confidentiality reasons)


    i've created the dynamic range as you did, (called ItemList) with the same formula (=OFFSET(Test!$A$1,0,0,COUNTA(Test!$A:$A),1)

    However the answer does not work as (in the formula =INDEX($A$1:$U$19,MATCH(B23,ItemList,0),MATCH(B22,$A$1:$U$1,1)) ) shows a value of zero.

    Is there something blindlingly obvious that iv missed?
    I hope that its possible to assess without seing the file

    many many thanks

    simon

  8. #8
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    ItemList is only for the col containing the items.

    1.You will have to create also a range for the entire table
    Let's call it PriceList defined as follows =offset($A$1,0,0,counta($a:$a),counta($1:$1))
    It will account for any items or quantities added

    2. Same thing for the quantities

    name : QtyList defined as =offset($A$1,0,0,1,counta($1:$1))

    Your formula then becomes
    =INDEX(Pricelist,MATCH(B23,ItemList,0),MATCH(B22,QtyList,1)) )

  9. #9
    Registered User
    Join Date
    08-05-2010
    Location
    midlands
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

    great thanks for your all help.

+ 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