Help With If Formula
 

I have the following monitoring tool:

Engine Trading
1 TRUE
1 TRUE
1 TRUE
2 TRUE
2 FALSE
2 TRUE

I am looking for a formula to place in ColC of this table that will look at
the monitoring tool above and if all values for a given engine are "true",
return the value that is in ColB. I've filled in the table below with the
result I am trying to achieve.

Engine String Status
1 ABC ABC
2 EFG

Thank you in advance.

Help With If Formula
 

I'll assume that the monitoring data is on Sheet1 and the table you're trying
to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2,
enter the formula
=if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"")
This counts the number of times FALSE appears with the particular engine,
and sets copies the value from b2 to c2 if the count is 0.
HTH. --Bruce


"carl" wrote:

I have the following monitoring tool:

Engine Trading
1 TRUE
1 TRUE
1 TRUE
2 TRUE
2 FALSE
2 TRUE

I am looking for a formula to place in ColC of this table that will look at
the monitoring tool above and if all values for a given engine are "true",
return the value that is in ColB. I've filled in the table below with the
result I am trying to achieve.

Engine String Status
1 ABC ABC
2 EFG

Thank you in advance.

Help With If Formula
 

Thank you Brian. I tried this formula but it returns the value in b2 even if
some values in Sheet1!B$1:B$10000="FALSE" are indeed false.

Am I doing something wrong ?

"bpeltzer" wrote:

I'll assume that the monitoring data is on Sheet1 and the table you're trying
to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2,
enter the formula
=if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"")
This counts the number of times FALSE appears with the particular engine,
and sets copies the value from b2 to c2 if the count is 0.
HTH. --Bruce


"carl" wrote:

I have the following monitoring tool:

Engine Trading
1 TRUE
1 TRUE
1 TRUE
2 TRUE
2 FALSE
2 TRUE

I am looking for a formula to place in ColC of this table that will look at
the monitoring tool above and if all values for a given engine are "true",
return the value that is in ColB. I've filled in the table below with the
result I am trying to achieve.

Engine String Status
1 ABC ABC
2 EFG

Thank you in advance.

Help With If Formula
 

Are the false values text or are they the results of logical comparisons? If
the latter, remove the quotes around the FALSE.
--Bruce

"carl" wrote:

Thank you Brian. I tried this formula but it returns the value in b2 even if
some values in Sheet1!B$1:B$10000="FALSE" are indeed false.

Am I doing something wrong ?

"bpeltzer" wrote:

I'll assume that the monitoring data is on Sheet1 and the table you're trying
to compile on Sheet2 with column headers in row 1. In cell C2 on Sheet2,
enter the formula
=if(sumproduct(--(Sheet1!A$1:A$10000=a2),--(Sheet1!B$1:B$10000="FALSE"))=0,b2,"")
This counts the number of times FALSE appears with the particular engine,
and sets copies the value from b2 to c2 if the count is 0.
HTH. --Bruce


"carl" wrote:

I have the following monitoring tool:

Engine Trading
1 TRUE
1 TRUE
1 TRUE
2 TRUE
2 FALSE
2 TRUE

I am looking for a formula to place in ColC of this table that will look at
the monitoring tool above and if all values for a given engine are "true",
return the value that is in ColB. I've filled in the table below with the
result I am trying to achieve.

Engine String Status
1 ABC ABC
2 EFG

Thank you in advance.