0

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.