Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
|
|||
|
|||
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:
This formula will count all the values in column K that meet the following criteria:
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT not equal to... | Excel Worksheet Functions | |||
SUMPRODUCT - How can I use does not equal in an array? | Excel Worksheet Functions | |||
GREATER OR EQUAL TO BUT LESS THAN Problem using Sumproduct | Excel Worksheet Functions | |||
SumProduct - Value ISN'T equal to | Excel Discussion (Misc queries) | |||
IF function... not equal to | Excel Worksheet Functions |