| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
addding/omitting "x"
colume a=value, colume b is notation with s1, s2, s3, and s4 according to the
type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
addding/omitting "x"
Hi there.
First of all, I guess you could continue updating your first post below ;) Regarding to your problem, please, try: =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20) SHIFT + CTRL + ENTER You should enter the above formula by pressing SHIFT + CTRL + ENTER (and not just ENTER) since it is an array formula. Regards, Otávio "tleehh" wrote: colume a=value, colume b is notation with s1, s2, s3, and s4 according to the type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
addding/omitting "x"
"Otávio Alves Ribeiro" wrote: Hi there. First of all, I guess you could continue updating your first post below ;) Regarding to your problem, please, try: =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20) SHIFT + CTRL + ENTER You should enter the above formula by pressing SHIFT + CTRL + ENTER (and not just ENTER) since it is an array formula. Regards, Otávio "tleehh" wrote: colume a=value, colume b is notation with s1, s2, s3, and s4 according to the type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
addding/omitting "x"
"tleehh" wrote: "Otávio Alves Ribeiro" wrote: Hi there. First of all, I guess you could continue updating your first post below ;) Regarding to your problem, please, try: =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20) SHIFT + CTRL + ENTER You should enter the above formula by pressing SHIFT + CTRL + ENTER (and not just ENTER) since it is an array formula. Regards, Otávio "tleehh" wrote: colume a=value, colume b is notation with s1, s2, s3, and s4 according to the type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. Thank you. using your given formula =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20), how can it add all the values in colume A that is prior to todays date (dates are in colume D). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
addding/omitting "x"
Hi there.
You may want to add an extra condition to the existing ones: =sum((B10:B20="s1")*(C10:C20<"x")*(D10:D20<today( ))*A10:A20) Regards, Otávio "tleehh" wrote: "tleehh" wrote: "Otávio Alves Ribeiro" wrote: Hi there. First of all, I guess you could continue updating your first post below ;) Regarding to your problem, please, try: =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20) SHIFT + CTRL + ENTER You should enter the above formula by pressing SHIFT + CTRL + ENTER (and not just ENTER) since it is an array formula. Regards, Otávio "tleehh" wrote: colume a=value, colume b is notation with s1, s2, s3, and s4 according to the type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. Thank you. using your given formula =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20), how can it add all the values in colume A that is prior to todays date (dates are in colume D). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
addding/omitting "x"
.... or =SUMPRODUCT((B10:B20="s1")*(C10:C20<"x")*(D10:D20 <today())*A10:A20)
or =SUMPRODUCT(--(B10:B20="s1"),--(C10:C20<"x"),--(D10:D20<today()),A10:A20) neither of which needs Control Shift Enter. -- David Biddulph "Otávio Alves Ribeiro" wrote in message ... Hi there. You may want to add an extra condition to the existing ones: =sum((B10:B20="s1")*(C10:C20<"x")*(D10:D20<today( ))*A10:A20) Regards, Otávio "tleehh" wrote: "tleehh" wrote: "Otávio Alves Ribeiro" wrote: Hi there. First of all, I guess you could continue updating your first post below ;) Regarding to your problem, please, try: =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20) SHIFT + CTRL + ENTER You should enter the above formula by pressing SHIFT + CTRL + ENTER (and not just ENTER) since it is an array formula. Regards, Otávio "tleehh" wrote: colume a=value, colume b is notation with s1, s2, s3, and s4 according to the type of values in colume a. I want to add only values that was notated with s1 in colume b, but omitting that was mark with "x" in colume c respectively. Thank you. using your given formula =sum((B10:B20="s1")*(C10:C20<"x")*A10:A20), how can it add all the values in colume A that is prior to todays date (dates are in colume D). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| addding/omitting "x" | Excel Worksheet Functions | |||
| deleting or omitting "-"'s in a group of numbers w/ a formula | Excel Discussion (Misc queries) | |||
| omitting/deleting "-"'s from numbers w/ formula | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Linear Mode
