Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 21 Posts

·
Registered
Joined
·
12 Posts
Discussion Starter · #1 ·
How do I combine these two formulas to work in the same cell?

=IF(AND(B22=TRUE,H22=FALSE),B21,IF(AND(B22=TRUE,H22=TRUE),H21+B21,0))

=IF(AND(B26=TRUE,H26=FALSE),B25,IF(AND(B26=TRUE,H26=TRUE),H25+B25,0))
 

·
Retired Trusted Advisor
Joined
·
4,042 Posts
Hi stud24, welcome to TSG.

We need a little more information. For example, what happens if B22=TRUE, H22=FALSE, B26=TRUE, and H26=FALSE? Would that return B21 or B25 or something different?
 

·
Registered
Joined
·
12 Posts
Discussion Starter · #5 ·
YES! thank you. now how do I add this in there?

IF(AND(E22=TRUE,F22=FALSE),B21+E21,IF(AND(F22=TRUE,E22=FALSE),F21+B21,0))

It will add values if the box is checked.
 

·
Retired Trusted Advisor
Joined
·
4,042 Posts
This is getting significantly more complicated, but try this:

=IF(B22=TRUE,IF(H22=TRUE,IF(E22=TRUE,B21+H21+E21,IF(F22=TRUE,B21+H21+F21,B21+H21)),IF(E22=TRUE,B21+E21,IF(F22=TRUE,B21+F21,B21))), IF(B26=TRUE,IF(H26=FALSE,B25,B25+H25 ),0))
 

·
Registered
Joined
·
12 Posts
Discussion Starter · #12 ·
yea never mind too complicated for me. If you dont mind one more thing to add in,

If D26=true + D25, If E26=true + E25, If F26=true + F25.

same rules apply
 

·
Retired Trusted Advisor
Joined
·
4,042 Posts
If you really need all of these calculations in one cell, I would suggest that you think about using a macro or splitting the formula into smaller parts instead. There's a limit to the number of nested IF statements you can have and this formula is fairly complex already. You're not able to troubleshoot the formula yourself, so if something changes you'll be stuck. It might make sense to use a couple of helper columns with simpler formulas and then hide those columns.
 

·
Retired Trusted Advisor
Joined
·
4,042 Posts
Thanks, that's very helpful. So are you basically trying to just add together all the numbers for the checked boxes? If so, try this:

=SUMIF(B22:H22,TRUE,A21:B21)+SUMIF(B26:H26,TRUE,A25:B25)
 

·
Registered
Joined
·
12 Posts
Discussion Starter · #17 ·
Kind of. If you see the red numbers A0431, A0434, A0436. If a box is checked under A0431 the price is added to to base rate (D41). the others can't interfere with it. Then the international boxes can not be check at same time as boxes above them.
 

·
Retired Trusted Advisor
Joined
·
4,042 Posts
Ok, is it working fine for you now? This should work:

=SUMIF(B22,TRUE,B21)+SUMIF(E22:F22,TRUE,E21:F21)+SUMIF(H21,TRUE,H21)+SUMIF(B26,TRUE,B25)+SUMIF(D26:F26,TRUE,D25:F25)+SUMIF(H26,TRUE,H25)

Or you consolidate it a little:

=SUM(SUMIF(INDIRECT({"b22","e22:f22","h22","b26","d26:f26","h26"}),TRUE,INDIRECT({"b21","e21:f21","h21","b25","d25:f25","h25"})))
 
1 - 20 of 21 Posts
Status
Not open for further replies.
Top