IF Formula, Sum and FALSE

[sjwhitaker]

I am sure the title doesn't help what I am having problems with, but was drawing a blank on what to put. ANYWAY! I am working on an IF Formula to do different things if different cells are True or False. This is what I have so far

=IF(B4:B6=FALSE,SUM(E3:E6),IF(B5:B6=FALSE,SUM(E3+E5),IF(B6=FALSE,SUM(E3+(E6/3)))))

1st Part - B4 to B6 will either read a name or FALSE, so if they are FALSE, I want to sum the values in E3 to E6

2nd Part - B5 to B6 will either read a name or FALSE, so if they are FALSE, I want the sum of E3 and E5

3rd Part - B6 will either read a name or FALSE, so if it is False I want to divide E6 by 3 and add that to E3

This is for placing earnings sheet, there will be 4 places that will pay, but it may not have 4 people, so I want to modify the winnings to accomidate how many people there actually are.

Hope that all makes sense!!!

 

[JimM]

Going to need a bit more info

In part 1 do all cells in B4:B6 have to be false or only 1 of them

What do you want to happen if there are no FALSEs in each case

 

[dscg]

I think your existing formula will do what you want, it's just how you enter it.
Each of these logical tests is checking an array: B4:B6=FALSE - 3 different cells.
To use an array formula, take the cell your existing formula is in, click on it, click in the formula bar, and then hit ctrl + shift + enter to confirm. If done successfully it will add the brackets like:

{=IF(B4:B6=FALSE,SUM(E3:E6),IF(B5:B6=FALSE,SUM(E3+E5),IF(B6=FALSE,SUM(E3+(E6/3)))))}

any time you change the formula, you'll need to confirm in the same way.

 

[sjwhitaker]

In Part 1, yes all cells would need to be False.
If Part 1 were False, then it would move to part 2, if part 2 was false then it would move to part 3 and if part 3 were false (forgot to have this in it, then it would equal E3)

 

[sjwhitaker]

I think your existing formula will do what you want, it's just how you enter it.
Each of these logical tests is checking an array: B4:B6=FALSE - 3 different cells.
To use an array formula, take the cell your existing formula is in, click on it, click in the formula bar, and then hit ctrl + shift + enter to confirm. If done successfully it will add the brackets like:

{=IF(B4:B6=FALSE,SUM(E3:E6),IF(B5:B6=FALSE,SUM(E3+E5),IF(B6=FALSE,SUM(E3+(E6/3)))))}

any time you change the formula, you'll need to confirm in the same way.

That worked!! Ok, now if I want it to equal E3 when all are FALSE, how would I write that?

 

[dscg]

Perhaps this would be better: This is a regular formula, not an array:

=IF(AND(B4=FALSE,B5=FALSE,B6=FALSE),E3,IF(AND(B5=FALSE,B6=FALSE),SUM(E3:E5),IF(B6=FALSE,SUM(E3+(E6/3)))))

What it doesn't take into account is if all have a name. Just enter that case here:

=IF(AND(B4=FALSE,B5=FALSE,B6=FALSE),E3,IF(AND(B5=FALSE,B6=FALSE),SUM(E3:E5),IF(B6=FALSE,SUM(E3+(E6/3)),"All contain text")))

 

[sjwhitaker]

THANKS GUYS!!! I got it figured out now