Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Hi All,
Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
hi
post your formula please. Regards FSt1 "Linda" wrote: Hi All, Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
=SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504)
"FSt1" wrote: hi post your formula please. Regards FSt1 "Linda" wrote: Hi All, Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
"Linda" wrote:
=SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) It's an array formula. If you array-enter it in the cell, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER. If you do it correctly, Excel will wrap curly braces around the formula: { }. You should see these braces in the formula bar as a visual check that the formula is correctly array entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Your formula is an *array* formula.
-- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. However, you could convert it to the Sumproduct() function, and then use a simple <Enter: =Sumproduct((Master!$A$6:$A$504="January")*(Master !$C$6:$C$504="LBK")*Master!$H$6:$H$504) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" wrote in message ... =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) "FSt1" wrote: hi post your formula please. Regards FSt1 "Linda" wrote: Hi All, Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Or, you could use this non-array version.
Normally entered: =SUMPRODUCT(--(Master!$A$6:$A$504="January"),--(Master!$C$6:$C$504="LBK"),Master!$H$6:$H$504) Better to use cells to hold the criteria: A1 = January B1 = LBK =SUMPRODUCT(--(Master!$A$6:$A$504=A1),--(Master!$C$6:$C$504=B1),Master!$H$6:$H$504) -- Biff Microsoft Excel MVP "Max" wrote in message ... "Linda" wrote: =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) It's an array formula. If you array-enter it in the cell, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER. If you do it correctly, Excel will wrap curly braces around the formula: { }. You should see these braces in the formula bar as a visual check that the formula is correctly array entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
SWEET, it worked!!! Thank you sooo much for that , I truly appreciate it!
Linda "Max" wrote: "Linda" wrote: =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) It's an array formula. If you array-enter it in the cell, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER. If you do it correctly, Excel will wrap curly braces around the formula: { }. You should see these braces in the formula bar as a visual check that the formula is correctly array entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Thanks a million!!!
Linda "T. Valko" wrote: Or, you could use this non-array version. Normally entered: =SUMPRODUCT(--(Master!$A$6:$A$504="January"),--(Master!$C$6:$C$504="LBK"),Master!$H$6:$H$504) Better to use cells to hold the criteria: A1 = January B1 = LBK =SUMPRODUCT(--(Master!$A$6:$A$504=A1),--(Master!$C$6:$C$504=B1),Master!$H$6:$H$504) -- Biff Microsoft Excel MVP "Max" wrote in message ... "Linda" wrote: =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) It's an array formula. If you array-enter it in the cell, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER. If you do it correctly, Excel will wrap curly braces around the formula: { }. You should see these braces in the formula bar as a visual check that the formula is correctly array entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Thanks so much!
Linda "RagDyer" wrote: Your formula is an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. However, you could convert it to the Sumproduct() function, and then use a simple <Enter: =Sumproduct((Master!$A$6:$A$504="January")*(Master !$C$6:$C$504="LBK")*Master!$H$6:$H$504) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" wrote in message ... =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) "FSt1" wrote: hi post your formula please. Regards FSt1 "Linda" wrote: Hi All, Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
You're welcome!
-- Biff Microsoft Excel MVP "Linda" wrote in message ... Thanks a million!!! Linda "T. Valko" wrote: Or, you could use this non-array version. Normally entered: =SUMPRODUCT(--(Master!$A$6:$A$504="January"),--(Master!$C$6:$C$504="LBK"),Master!$H$6:$H$504) Better to use cells to hold the criteria: A1 = January B1 = LBK =SUMPRODUCT(--(Master!$A$6:$A$504=A1),--(Master!$C$6:$C$504=B1),Master!$H$6:$H$504) -- Biff Microsoft Excel MVP "Max" wrote in message ... "Linda" wrote: =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) It's an array formula. If you array-enter it in the cell, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER, it'll return the correct result. Just click inside the formula bar again, then press CTRL+SHIFT+ENTER. If you do it correctly, Excel will wrap curly braces around the formula: { }. You should see these braces in the formula bar as a visual check that the formula is correctly array entered. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
welcome, Linda.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Linda" wrote in message ... SWEET, it worked!!! Thank you sooo much for that , I truly appreciate it! Linda |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function argument not returning a value
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" wrote in message ... Thanks so much! Linda "RagDyer" wrote: Your formula is an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. However, you could convert it to the Sumproduct() function, and then use a simple <Enter: =Sumproduct((Master!$A$6:$A$504="January")*(Master !$C$6:$C$504="LBK")*Master!$H$6:$H$504) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" wrote in message ... =SUM((Master!$A$6:$A$504="January")*(Master!$C$6:$ C$504="LBK")*Master!$H$6:$H$504) "FSt1" wrote: hi post your formula please. Regards FSt1 "Linda" wrote: Hi All, Quick question: I'm using the SUM fx in order to create a formula that would return a value based on information in a different worksheet within the same workbook. When I key in the formula and hit ENTER, I get a 0 in the cell of interest. However, when I click on the fx button at the top, it shows my formula and my formula result as 20.75. Anyone know why the cell won't show 20.75 if the fx argument is calculating correct? Thanks a million, Linda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get around 30 argument limit of SUM function? | Excel Worksheet Functions | |||
VBA function for "Mean" using Array as argument | Excel Worksheet Functions | |||
Need Function Argument | Excel Worksheet Functions | |||
text as an argument of SUM function | Excel Worksheet Functions | |||
How do I specify more than one argument for the SUMIF function? | Excel Worksheet Functions |