Sumproduct with Gaps

[JimS]

I tried using this formula:

=SUMPRODUCT(--(R25:R3000="o")*(S25:S3000="w"))

The problem is that there are gaps and blank cells down through the
spreadsheet.

How can I count the number of times "o" corresponds with "w" when
there are blank cells in the spread sheet?

 

[Pete_UK]

Try this:

=SUMPRODUCT((R25:R3000="o")*(S25:S3000="w")*(R25:R3000<>"")*
(S25:S3000<>""))

Hope this helps.

Pete

 

[Dave Peterson]

Use that formula. It'll ignore the empty cells--since the those empty cells
won't equal o or w.

 

[JimS]

Yes, that's very slick--works quite well. Thanks a lot.

 

[Dave Peterson]

ps.

The -- aren't needed in your formula. They're used to coerce True/falses to
1/0's. And the multiplication (*) does that fine.

so either:
=SUMPRODUCT((R25:R3000="o")*(S25:S3000="w"))
or
=SUMPRODUCT(--(R25:R3000="o"),--(S25:S3000="w"))