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

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

Simon

2. ## 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. ## 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)

4. ## 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

5. ## 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. ## Re: SUM PRODUCT QUERY to lookup a query based on item type and item price range

Sorry Arthurbr

That's amazing!

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

Great help

7. ## 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. ## 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))

=INDEX(Pricelist,MATCH(B23,ItemList,0),MATCH(B22,QtyList,1)) )

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

great thanks for your all help.

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

#### 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