=SUMPRODUCT(--('Raw Data'!$E1:$E65535<=4))

How do I change the 65535 in above formula to dynamic variable?


Regards,
Tee

Untested but should work - assuming that c5 has the dynamic number in it.
=SUMPRODUCT(--(indirect("Raw Data'!$E1:$E"&c5)<=4))
--
Wag more, bark less


" wrote:

=SUMPRODUCT(--('Raw Data'!$E1:$E65535<=4))

How do I change the 65535 in above formula to dynamic variable?


Regards,
Tee

I have tested but not work #REF! error.

On Jun 11, 9:51*am, Brad wrote:
Untested but should work - assuming that c5 has the dynamic number in it.
=SUMPRODUCT(--(indirect("Raw Data'!$E1:$E"&c5)<=4))
--
Wag more, bark less



" wrote:
=SUMPRODUCT(--('Raw Data'!$E1:$E65535<=4))

How do I change the 65535 in above formula to dynamic variable?

Regards,
Tee- Hide quoted text -

- Show quoted text -

give this a try:

create a named range (insert-name-define) with the following ( i used rng as
the name):
(this creates a dynamic range)

=OFFSET('Raw Data'!$E$1,1,0,COUNTA('Raw Data'!$E:$E)-1,1)

then for your formula:

=SUMIF(rng,"<=4",rng)
or
=SUMPRODUCT(--(rng)*(rng<=4))


--


Gary


wrote in message
...
I have tested but not work #REF! error.

On Jun 11, 9:51 am, Brad wrote:
Untested but should work - assuming that c5 has the dynamic number in it.
=SUMPRODUCT(--(indirect("Raw Data'!$E1:$E"&c5)<=4))
--
Wag more, bark less



" wrote:
=SUMPRODUCT(--('Raw Data'!$E1:$E65535<=4))

How do I change the 65535 in above formula to dynamic variable?

Regards,
Tee- Hide quoted text -

- Show quoted text -

Thas been tested and does work
=SUMPRODUCT(--(INDIRECT("'Raw Data'!E2:E"&B4)<=4))



--
Wag more, bark less


" wrote:

I have tested but not work #REF! error.

On Jun 11, 9:51 am, Brad wrote:
Untested but should work - assuming that c5 has the dynamic number in it.
=SUMPRODUCT(--(indirect("Raw Data'!$E1:$E"&c5)<=4))
--
Wag more, bark less



" wrote:
=SUMPRODUCT(--('Raw Data'!$E1:$E65535<=4))

How do I change the 65535 in above formula to dynamic variable?

Regards,
Tee- Hide quoted text -

- Show quoted text -