Array to produce sum

[Grayling]

Cells B3:B30, individually, either contain numbers or
text or are blank. Alongside, in A3:A30, are cells with
text. I'd like, in B1, the sum of only those cells in
the B column that have a corresponding A cell that
contains a "D" somewhere in the text. I thought

=SUM(IF(A3:A30="W",B3:Z30))

would do it, entered with a ctrl-shift-enter array
flourish. But I get a #VALUE! error.

I'd be grateful for help. Many thanks

Grayling

 

[Don Guillett]

No array needed, just use sumif without CSE
=SUMIF(a3:a30,"b",b3:b30) will do col B value

=SUM(IF(A3:A30="W",B3:Z30))

=SUMPRODUCT((a3:a30="b")*b3:z30) to do a range

 

[Guest]

Thanks. Who needs arrays! Not quite there yet though.
=SUMIF(a3:a30,"b",b3:b30) works but only sums cells where
the corresponding cell is "b", but misses the other cells
where the corresponding cell contains "b", e.g. "bJ".
I've got a sneaking that I need to look at the FIND
function.

Regards

Grayling

 

[Dave R.]

Go with that "sneaking" (I think you mean "sneaking feeling" or something)
and try ISNUMBER(FIND(A3:A30,"b"))

 

[Frank Kabel]

Hi
try
=SUMIF(a3:a30,"*b*",b3:b30)

 

[Guest]

Got it. Thanks to all.

Grayling