May 07 2019 02:34 PM
Hello,
I am working on a project and I am needing to reference three different ranges(prices) of cells based on pricing levels below. I have seven different prices/parts I need to have a formula to add together and total and when it hits $2501-$10,000 it needs to use the second range of prices, and the third range of pricing for total @ and over $25,000
=>2500
>10,000<
=<25,000
Thank you,
Joshua
May 07 2019 10:38 PM
You stated "I need to have a formula to add together and total and when it hits $2501-$10,000 it needs to use the second range of prices, and the third range of pricing for total @ and over $25,000"
What does "it" refer to? It cannot refer to total in your sample file because it will result in circular reference error.
May 08 2019 07:47 AM
@jmorr08u , that could be
=G2*IF(SUMPRODUCT($G$2:$G$8*$L$2:$L$8)>$L$1,$K2, IF(SUMPRODUCT($G$2:$G$8*$M$2:$M$8)>$M$1,$L2,$M2))
in I2 and drag it down. I removed multiplier 100 in initial formula since it wasn't explained how to use it, Cost=Qty*Price here