SUMPRODUCT formula query

A

anthony slater

This works

=SUMPRODUCT(($C$5:$C$3000>=1)*($C$5:$C$3000<=1799))

So why doesn't this work?

=SUMPRODUCT(($C$5:$C$3000>=1)*($C$5:$C$3000<=1799)*
($B$5:$B$2894="Business"))

Basically, I want to count the amout of cells that are
bewteen 1 and 1799 in coloumn C and also equal "Business"
in column B

Am I approaching this the wrong way?
 
F

Frank Kabel

Hi
the ranges have to be identical. Use:
=SUMPRODUCT(($C$5:$C$3000>=1)*($C$5:$C$3000<=1799)*
($B$5:$B$3000="Business"))
 
B

BenjieLop

Your B column must be _$B$5:*$B$3000__*_ (and NOT _$B$5:*$B$2894*)__ for
your formula to work.


anthony said:
This works

=SUMPRODUCT(($C$5:$C$3000>=1)*($C$5:$C$3000<=1799))

So why doesn't this work?

=SUMPRODUCT(($C$5:$C$3000>=1)*($C$5:$C$3000<=1799)*
($B$5:$B$2894="Business"))

Basically, I want to count the amout of cells that are
bewteen 1 and 1799 in coloumn C and also equal "Business"
in column B

Am I approaching this the wrong way?
Click to expand...
 
D

Don Guillett

You can preclude this error in range size in the future by naming your
ranges and have them all refer to a counta for one range.
rngB
=offset($b$5,0,0,counta($b:$b),1)
rngC
=offset($c$5,0,0,counta($b:$b),1)
notice the count of col B in both cases.
 
Top