Sumproducting counting blanks

[CAD]

Right now I have the below formula that I thought was correct, but it is counting blanks. What I want the formula to return is the count when anything in column H is greater or equal to 1 when the corresponding row in W is less than or equal to 2. Right now the below formula is counting blanks. Any ideas?


=SUMPRODUCT(--($H$7:$H$78>=1),--($H$7:$H$78<>""),--(W7:W78<=2))

Thanks
CAD

 

[Claus Busch]

Hi,

Am Thu, 11 Jul 2013 13:53:36 -0700 (PDT) schrieb CAD:

Right now I have the below formula that I thought was correct, but it is counting blanks. What I want the formula to return is the count when anything in column H is greater or equal to 1 when the corresponding row in W is less than or equal to 2. Right now the below formula is counting blanks. Any ideas?

=SUMPRODUCT(--($H$7:$H$78>=1),--($H$7:$H$78<>""),--(W7:W78<=2))

try:
=SUMPRODUCT(--(H7:H78>=1),--(W7:W78<=2))
or:
=COUNTIFS(H7:H78,">=1",W7:W78,"<=2")


Regards
Claus B.

 

[CAD]

Countifs worked. Thanks!

 

[PeCoNe]

Op 2013-07-12 08:06, Claus Busch schreef:

Hi,

Am Thu, 11 Jul 2013 13:53:36 -0700 (PDT) schrieb CAD:


try:
=SUMPRODUCT(--(H7:H78>=1),--(W7:W78<=2))
or:
=COUNTIFS(H7:H78,">=1",W7:W78,"<=2")


Regards
Claus B.

Hi Claus,

What is the function of the --?

Bye Peter

 

[Claus Busch]

Hi Peter,

Am Fri, 12 Jul 2013 17:33:44 +0200 schrieb PeCoNe:

What is the function of the --?

that will change the boolean values to integers.


Regards
Claus B.