Multiple if statements with multiple conditions

[egarcia]

a b
1 TRUE 10
2 TRUE 8
3 2
4 4
5 TRUE 5
I'm trying to get an if function to work with multiple conditions. For
example if(a1=true,a2=true,a5=true,sum(b3:b4), if
(a1=true,a2=true,a5=false,sum(b3:b5), if
a1=false,a2=false,a5=true,sum(b1:b4). First condition should =6, second=11, &
third should be 24.

 

[Roger Govier]

Hi

Try
= if(and(a1=true,a2=true,a5=true),sum(b3:b4,
if(and(a1=true,a2=true,a5=false),sum(b3:b5),
if(and(a1=false,a2=false,a5=true),sum(b1:b4)."" )))

 

[egarcia]

Here is the exact formula, it returns false instead of a value.

IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary Sheet'!M32:M41)))))))

 

[Bob Phillips]

Return a value for me, but doesn't need the last test

=IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,Worksheet!N360),SUM('Summary
Sheet'!M32:M40),SUM('Summary Sheet'!M32:M41))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Roger Govier]

You are missing the final false argument.
I have assumed you want Null "" if all cases fail to be satisfied. It is
inserted after the final SUM() term.

=IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,Worksheet!N360=FALSE),
SUM('Summary Sheet'!M32:M41,"")))))))