Logical Formulas

Copper Contributor

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 

2 Replies

@jmorr08u 

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

 

@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