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

#### r3blox

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

##### Well-known Member
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

##### New Member
Thank you!! That works perfectly!!

you're welcome

1,081,425
Messages
5,358,604
Members
400,505
Latest member
JacquiT

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...