# xls how can SUMPRODUCT handle boolean?

i have a formula here

=sumproduct((sheet1!$A$2:$A$3000=$A69)*(sheet1!$E$1:$F$1=$D69)*(sheet1!G$2:G$3000)*(sheet1!$E$2:$F$3000))


it produce some value,

BUT when i execute this

=(sheet1!$A$2:$A$3000=\$A69)


it produce boolean (TRUE/FALSE),

how can xls product a value with boolean as array?

You are multiplying that array with other arrays that produce either boolean values or (presumably) contain numbers, so when you multiply an array containing TRUE/FALSE values the TRUE values act like 1s and the FALSE values like zeroes.

For example if I multiply two arrays like this:

={TRUE,TRUE,FALSE,FALSE}*{TRUE,FALSE,TRUE,FALSE}

the result is

={1,0,0,0}

or with this

={TRUE,TRUE,FALSE,FALSE}*{1,2,3,4}

I get the result

={1,2,0,0}

The result for your formula is the sum of the product of all values in E2:F3000 where the relevant row labels/column headers match A69/D69, multiplied by the relevant row in G2:G3000