Using IF, AND & similar functions to determine cell value based on factors

[r3blox]

Hello everyone,

I've been at this for 3 days now, all over forums and google with no luck. Refer to the picture I posted in this post for reference, but I need help calculating the monthly bill amount based on various factors.

Basically:

1. If there are no values in a row from columns K through Q, then that row's R cell will show $49.99. If the sum of K through N (section labeled here as ADDON 1) is 1 or 2, R automatically changes to $60.99. BUT, if the sum of K through N is equal to 3 or higher, you add $5 per number over 2.
   1. Example: Someone orders one doorbell camera and a smart t-stat, their monthly price is $60.99. If they order a doorbell camera, t-stat and a doorlock, its $5 more for that third item. Let's say they order 5 items. That's $60.99 for the first 2, plus $15 for the final 3 items, for a total of $75.99.
2. The exact same concept for columns O through Q (ADDON 2), except it automatically adds $10 per item starting from the first.
   1. Example: Someone doesn't order anything from section 1, just something from section 2, like a space monkey. Their $49.99 increases $10, for $59.99. If they order 2 or less items from section 1 (auto jumps to $60.99) plus one item from section 2, their bill is $70.99.

Hopefully that makes sense. I could really use some help. Here's the current formula I came up with for the total of column R, but I know I'm doing something (or several things) wrong....

=IF(SUM(K8:Q8)<1,"$49.99",IF(0<SUM(K8:N8)<3,"$60.99",IF(AND(SUM(K8:N8)=3,SUM(O8:Q8)=0),"$65.99","$60.99")))

Thank you in advance if this makes sense and you're able to help me find a formula!

 

[AlanY]

try this in Column R

=IF(SUM(K2:Q2)=0,49.99,10*SUM(O2:Q2)+60.99+IF(SUM(K2:N2)=0,-11,IF(SUM(K2:N2)<3,0,5*(SUM(K2:N2)-2))))

 

[r3blox]

Thank you!! That works perfectly!!

 

[AlanY]

you're welcome