ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Multiple if statements with multiple conditions (https://www.excelbanter.com/excel-discussion-misc-queries/128214-multiple-if-statements-multiple-conditions.html)

egarcia
Multiple if statements with multiple conditions
 
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
Multiple if statements with multiple conditions
 
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)."" )))

--
Regards

Roger Govier


"egarcia" wrote in message
...
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.

egarcia
Multiple if statements with multiple conditions
 
Here is the exact formula, it returns false instead of a value.

IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wor ksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary Sheet'!M32:M41)))))))

"Roger Govier" wrote:

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)."" )))

--
Regards

Roger Govier


"egarcia" wrote in message
...
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.

Bob Phillips
Multiple if statements with multiple conditions
 
Return a value for me, but doesn't need the last test

=IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wo rksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Wo rksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Wo rksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wor ksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,W orksheet!N360),SUM('Summary
Sheet'!M32:M40),SUM('Summary Sheet'!M32:M41))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"egarcia" wrote in message
...
Here is the exact formula, it returns false instead of a value.

IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wor ksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M32:M41)))))))

"Roger Govier" wrote:

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)."" )))

--
Regards

Roger Govier


"egarcia" wrote in message
...
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
Multiple if statements with multiple conditions
 
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,Wo rksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Wo rksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=FALSE,Wo rksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),
IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wor ksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,W orksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),
IF(AND(Worksheet!N356=FALSE,Worksheet!N358=FALSE,W orksheet!N360=FALSE),
SUM('Summary Sheet'!M32:M41,"")))))))


--
Regards

Roger Govier


"egarcia" wrote in message
...
Here is the exact formula, it returns false instead of a value.

IF(AND(Worksheet!N356=TRUE,Worksheet!N358=TRUE,Wor ksheet!N360=TRUE),SUM('Summary
Sheet'!M34:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M33:M41),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M33:M40),IF(AND(Worksheet!N356=TRUE,Workshe et!N358=TRUE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M34:M41),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=TRUE),SUM('Summary
Sheet'!M32:M40),IF(AND(Worksheet!N356=FALSE,Worksh eet!N358=FALSE,Worksheet!N360=FALSE),SUM('Summary
Sheet'!M32:M41)))))))

"Roger Govier" wrote:

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)."" )))

--
Regards

Roger Govier


"egarcia" wrote in message
...
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.


All times are GMT +1. The time now is 12:39 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com