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
Bookmarks