Using booleans in sumproduct formulas to extract boolean range
 

I have a list of option with bolleans associated with them in range A1:B3:

Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Then I have data list which uses these Options as validation items in range
C1:C7:
Option 1
Option 1
Option 1
Option 2
Option 2
Option 2
Option 3

I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans
{1,1,1,1,1,1,0,}.

I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following
below.

SUMPRODUCT(--(C1:C7=TRUE))

How do I do the same considering the the values in C1:C7 are not expressed
as TRUE/FALSE. I need to include a lookup extract the booleans.

Using booleans in sumproduct formulas to extract boolean range
 

Since Option 1 and 2 are effectively the same thing here (both TRUE), then
why not test for condition to be not equal to Option 3?

=SUMPRODUCT(--(C1:C7<"Option 3"))

HTH,
Elkar


"ExcelMonkey" wrote:

I have a list of option with bolleans associated with them in range A1:B3:

Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Then I have data list which uses these Options as validation items in range
C1:C7:
Option 1
Option 1
Option 1
Option 2
Option 2
Option 2
Option 3

I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans
{1,1,1,1,1,1,0,}.

I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following
below.

SUMPRODUCT(--(C1:C7=TRUE))

How do I do the same considering the the values in C1:C7 are not expressed
as TRUE/FALSE. I need to include a lookup extract the booleans.

Using booleans in sumproduct formulas to extract boolean range
 

All three options can be TRUE or FALSE. So I cannot guarantee that doing
what you say will suffice when the user starts changing inputs.

Thanks

EM

"Elkar" wrote:

Since Option 1 and 2 are effectively the same thing here (both TRUE), then
why not test for condition to be not equal to Option 3?

=SUMPRODUCT(--(C1:C7<"Option 3"))

HTH,
Elkar


"ExcelMonkey" wrote:

I have a list of option with bolleans associated with them in range A1:B3:

Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Then I have data list which uses these Options as validation items in range
C1:C7:
Option 1
Option 1
Option 1
Option 2
Option 2
Option 2
Option 3

I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans
{1,1,1,1,1,1,0,}.

I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following
below.

SUMPRODUCT(--(C1:C7=TRUE))

How do I do the same considering the the values in C1:C7 are not expressed
as TRUE/FALSE. I need to include a lookup extract the booleans.

Using booleans in sumproduct formulas to extract boolean range
 

Sorry, I misunderstood your post. Although, I'm still not sure what your
final goal here is. This isn't a SUMPRODUCT, but see if this helps any:

=SUM(IF(C1:C7=$A$1,$B$1,0)+IF(C1:C7=$A$2,$B$2,0)+I F(C1:C7=$A$3,$B$3,0))

This is an array formula and should be entered with CTRL-SHIFT-ENTER instead
of just ENTER. If done properly, the formula should be enclosed in { }.

HTH,
Elkar


"ExcelMonkey" wrote:

All three options can be TRUE or FALSE. So I cannot guarantee that doing
what you say will suffice when the user starts changing inputs.

Thanks

EM

"Elkar" wrote:

Since Option 1 and 2 are effectively the same thing here (both TRUE), then
why not test for condition to be not equal to Option 3?

=SUMPRODUCT(--(C1:C7<"Option 3"))

HTH,
Elkar


"ExcelMonkey" wrote:

I have a list of option with bolleans associated with them in range A1:B3:

Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Then I have data list which uses these Options as validation items in range
C1:C7:
Option 1
Option 1
Option 1
Option 2
Option 2
Option 2
Option 3

I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans
{1,1,1,1,1,1,0,}.

I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following
below.

SUMPRODUCT(--(C1:C7=TRUE))

How do I do the same considering the the values in C1:C7 are not expressed
as TRUE/FALSE. I need to include a lookup extract the booleans.

Using booleans in sumproduct formulas to extract boolean range
 

Yes this may work, but not necessarily in the capacity I want to use it in.
What I am doing is a query on a list of items in an excel spreadsheet. I am
doing the query using a sumproduct function to act as a sumif with mulitple
conditions.

SUMPRODUCT(Condition1,Condition2,Condition3)

Each condition is preceeded by the unary operator "--"

SUMPRODUCT(--(D1:D20="Blue",E1:E20="North",F1:F20="Hot"))

I now want to add a condition. Say G1:G20 have one of the following values:
Option1, Option2 or Option3 which are in A1:A3. I want to be able to pull
this array of values into the sumproudct and extract their TRUE/FALSE
counterparts (B1:B3). This would be easier if G1:20 had the actual
TRUE/FALSE values themselves in it. But it doesn't. So I need a way of
doing a lookup

SUMPRODUCT(--(D1:D20="Blue",E1:E20="North",F1:F20="Hot"),
--SomeFunction(G1:G20 = TRUE))

Thanks
EM

"Elkar" wrote:

Sorry, I misunderstood your post. Although, I'm still not sure what your
final goal here is. This isn't a SUMPRODUCT, but see if this helps any:

=SUM(IF(C1:C7=$A$1,$B$1,0)+IF(C1:C7=$A$2,$B$2,0)+I F(C1:C7=$A$3,$B$3,0))

This is an array formula and should be entered with CTRL-SHIFT-ENTER instead
of just ENTER. If done properly, the formula should be enclosed in { }.

HTH,
Elkar


"ExcelMonkey" wrote:

All three options can be TRUE or FALSE. So I cannot guarantee that doing
what you say will suffice when the user starts changing inputs.

Thanks

EM

"Elkar" wrote:

Since Option 1 and 2 are effectively the same thing here (both TRUE), then
why not test for condition to be not equal to Option 3?

=SUMPRODUCT(--(C1:C7<"Option 3"))

HTH,
Elkar


"ExcelMonkey" wrote:

I have a list of option with bolleans associated with them in range A1:B3:

Option 1 TRUE
Option 2 TRUE
Option 3 FALSE

Then I have data list which uses these Options as validation items in range
C1:C7:
Option 1
Option 1
Option 1
Option 2
Option 2
Option 2
Option 3

I want to pull the range in C1:C7 into a SUMPRODUCT formula in the form
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,} and then coverted to booleans
{1,1,1,1,1,1,0,}.

I know that if C1:C7 were excpressed as TRUE/FALSE I would do thei following
below.

SUMPRODUCT(--(C1:C7=TRUE))

How do I do the same considering the the values in C1:C7 are not expressed
as TRUE/FALSE. I need to include a lookup extract the booleans.