Sumproduct

K

Karin Iversen

Hello,

I want to refer to at cell in sumproduct - it works when I use the formular:

=SUMPRODUCT(('data'!$A$23:$A$500=$A$1)*('data'!$B$23:$B$500={"26852030",
"28563210"})*(data'!$D$23:$D$500))

but it soes not work if i in the formular refer to a cell (B2) with
{"26852030", "28563210"} in then the results is 0. Is it possible to refer to
a cell

=SUMPRODUCT(('data'!$A$23:$A$500=$A$1)*('data'!$B$23:$B$500=B2)*(data'!$D$23:$D$500))

Hope someone can help with this.
Karin
 
D

Don Guillett

maybe by using indirect
or have a list and refer to list such as {b2,b3,b4}
 
B

Bob Phillips

You can use an array in the formula as you show, but if you want to refer to
cells, the array must be in a range, one value per cell, then refer to that
range. Just be aware that if the range of values is columnar, you will need
to TRANSPOSE it.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top