View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ExcelMonkey ExcelMonkey is offline
external usenet poster
 
Posts: 553
Default 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.