Sum-Product-Question

[Peter]

This formula works:

=SUMPRODUCT(--($C$17:$C$200<>"")*--($K$17:$K$200="bjh"))

It returns an answer of 8, which is correct.

But I need to drag this down for fifty columns. If I do that I'll
have to change the part where I have "bjh" for all fifty rows. Can I
somehow insert the cell it is referring to instead--O17 and then just
drag it down? Obviously it didn't work when I tried it, but it must
be a formatting problem.

Appreciate this newsgroup

 

[Don Guillett]

try replacing your * with a ,

 

[Peter]

That returns a "#value!" error.

 

[Don Guillett]

I just re-read and it does work and you can certainly copy down where cell
o17 contains bjh
If o18=sam then it would look for sam in col K. If you want it to look for
bjh for 50 use $o$17 to make the reference absolute or just name it and use
that. BTW, * or , both seem to work.

 

[Peter]

I am inserting this formula in p17. In my original formula I have
bjh, but I need to change this somehow to o17 in order to drag down.
Do I just substitute bjh for o17? I tried it and it didn't work.

I appreciate your help.

 

[Don Guillett]

Post YOUR formula efforts
Both do the same
=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200=O2))
=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200="bjh"))

 

[Peter]

Thank you for your patience. I found my error. I was putting
quotation marks around the o2 and getting a zero value. Now it works
fine.

Thanks again.

 

[Don Guillett]

Glad you finally got it sorted.