SUMPRODUCT Can I use less than or equal to with this function
 

Hi there,

I'm using this formula but can I amend it so that it counts less than or
equal to 35 rather thatn just 35?

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010=35))

Grateful for any help

Cheers
--
Mifty

Answer: SUMPRODUCT Can I use less than or equal to with this function
 

Hi Mifty,

Yes, you can definitely use less than or equal to with the SUMPRODUCT function. To modify your formula to count values less than or equal to 35, you can use the less than or equal to operator (<=) instead of the equal to operator (=) in the last criteria of your formula.

Here's the modified formula:

1. =SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010<=35))

This formula will count all the values in column K that meet the following criteria:

1. The corresponding value in column C equals the value in cell R2
2. The corresponding value in column E equals "Mat"
3. The corresponding value in column F equals "AT1"
4. The corresponding value in column K is less than or equal to 35

SUMPRODUCT Can I use less than or equal to with this function
 

Try this:

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010<=35))

I prefer this method. Though the result is the same.
=SUMPRODUCT(--($C$2:$C$16010=R2),--($E$2:$E$16010="Mat"),--($F$2:$F$16010="AT1"),--($K$2:$K$16010<=35))

HTH,
Paul

--

"Mifty" wrote in message
...
Hi there,

I'm using this formula but can I amend it so that it counts less than or
equal to 35 rather thatn just 35?

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010=35))

Grateful for any help

Cheers
--
Mifty

SUMPRODUCT Can I use less than or equal to with this function
 

Su

...........*($K$2:$K$16010<=35))

However, if there are any empty cells in that range they will meet the
condition of <=35. If you don't want the empty cells counted then you need
to add another test:

...........*($K$2:$K$16010<=35)*($K$2:$K$16010<"" ))


--
Biff
Microsoft Excel MVP


"Mifty" wrote in message
...
Hi there,

I'm using this formula but can I amend it so that it counts less than or
equal to 35 rather thatn just 35?

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010=35))

Grateful for any help

Cheers
--
Mifty

SUMPRODUCT Can I use less than or equal to with this function
 

Thank you Paul :-)

Cheers
--
Mifty


"PCLIVE" wrote:

Try this:

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010<=35))

I prefer this method. Though the result is the same.
=SUMPRODUCT(--($C$2:$C$16010=R2),--($E$2:$E$16010="Mat"),--($F$2:$F$16010="AT1"),--($K$2:$K$16010<=35))

HTH,
Paul

--

"Mifty" wrote in message
...
Hi there,

I'm using this formula but can I amend it so that it counts less than or
equal to 35 rather thatn just 35?

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010=35))

Grateful for any help

Cheers
--
Mifty

SUMPRODUCT Can I use less than or equal to with this function
 

You're welcome. However, please make note of T. Valko's point about column
K values that are either empty.

--

"Mifty" wrote in message
...
Thank you Paul :-)

Cheers
--
Mifty


"PCLIVE" wrote:

Try this:

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010<=35))

I prefer this method. Though the result is the same.
=SUMPRODUCT(--($C$2:$C$16010=R2),--($E$2:$E$16010="Mat"),--($F$2:$F$16010="AT1"),--($K$2:$K$16010<=35))

HTH,
Paul

--

"Mifty" wrote in message
...
Hi there,

I'm using this formula but can I amend it so that it counts less than
or
equal to 35 rather thatn just 35?

=SUMPRODUCT(($C$2:$C$16010=R2)*($E$2:$E$16010="Mat ")*($F$2:$F$16010="AT1")*($K$2:$K$16010=35))

Grateful for any help

Cheers
--
Mifty