I have a potentially simple problem that I cannot figure out in excel. I want to use a formula to sum cells if they meet two criteria. If the cells are less than 100 and S1 is 100 then I want to add 300 to the total sum. However, if the cells are greater than or equal to 100, I would like to just sum the cells regardless of what is in cell S1.
A1 A2 A3 X X S1 S2
1 1 1 100 100
If the sum of A2:C2 are less than 100 AND S2 equals 100 THEN add 300
If the sum of A2:C2 are greater than 100 AND S2 equals 100 THEN just show what the original sum
I have tried the following:
=IF(SUM(D2:H2)+SUM(P2:S2)=0,0,IF(SUM(D2:H2)+SUM(P2:S2)<100,IF(R2=100,SUM(D2:H2)+SUM(P2:S2)+SUM(300)),SUM(D2:H2)+SUM(P2:S2)))
Try something like:
=IF(AND(SUM(D2:H2)<100,S2=100),SUM(D2:H2)+300,SUM(D2:H2))
Will do the following:
If both the sum of d2 to h2 < 100 and s2 = 100, sum d2 to h2 and add 300
If both conditions are not true it will just return the sum of d2 to h2
Think I've got your logic right here but if not/you can't take this and make it work correct me and I'll figure it out.
(PS at the risk of being too much of a nerd you don't state what happens if the sum of D2:H2 = 100!)