Sumproduct with Gaps

  • Thread starter Thread starter JimS
  • Start date Start date
J

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?
 
Try this:

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

Hope this helps.

Pete
 
Use that formula. It'll ignore the empty cells--since the those empty cells
won't equal o or w.
 
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"))
 
Back
Top