| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use named ranges in array formula
I am trying to use named ranges in an array formula and it won't work. Below
is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Try:
=D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
When I enter the formula you show, I only get the #Value error. If I enter
the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Try this as an array* formula:
=D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work.. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M*arch")*Replaceme nts!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Sorry, I forgot about the named ranges. Try this:
=D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M**arch")*Replacem ents!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
I still get the #N/A error when entered as array. I have triple checked my
named ranges and the respective columns are as described. Any other suggestions. Thank you. "Pete_UK" wrote: Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="MÂ*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="MÂ*Â*arch")*Replac ements!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Does the range RMO actually contain month names, or are these dates?
Pete On Feb 26, 7:13 pm, Jan wrote: I still get the #N/A error when entered as array. I have triple checked my named ranges and the respective columns are as described. Any other suggestions. Thank you. "Pete_UK" wrote: Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M***arch")*Replace ments!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Toppers' array formula should work. No real need for the IF.
Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0)) Biff "Pete_UK" wrote in message oups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M**arch")*Replacem ents!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
The month name is text.
"Pete_UK" wrote: Does the range RMO actually contain month names, or are these dates? Pete On Feb 26, 7:13 pm, Jan wrote: I still get the #N/A error when entered as array. I have triple checked my named ranges and the respective columns are as described. Any other suggestions. Thank you. "Pete_UK" wrote: Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="MÂ*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="MÂ*Â*Â*arch")*Repl acements!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Hi Biff,
I've come across that myself occasionally when copying formulae from the newsgroups - sometimes you get awkward hyphens inserted. As regards the OP's problem, I feel it must be something to do with the data, as both formulae should work OK (unless there are those unwanted hypens lurking around). Pete On Feb 26, 7:27 pm, "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0)) Biff "Pete_UK" wrote in message oups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M***arch")*Replace ments!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
With the non array alternative I get the #Value error. This is really
strange....I thought any number of the solutions provided would have worked. More info if it will help. Column C (RBU) I have used a validation list to select Business Unit. I have used a validation list for Column K (RMO) to store the month (text), and column N is a numeric value. Any other ideas? Regarding Biff's comment for =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-Â*arch"),RLeas,0))...yes, I got the same result when I copied and pasted. Even though I corrected it, it still didn't work. "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-Â*arch"),RLeas,0)) Biff "Pete_UK" wrote in message oups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="MÂ*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="MÂ*Â*arch")*Replac ements!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
What does D8 contain then, Jan?
Pete On Feb 26, 7:45 pm, Jan wrote: With the non array alternative I get the #Value error. This is really strange....I thought any number of the solutions provided would have worked. More info if it will help. Column C (RBU) I have used a validation list to select Business Unit. I have used a validation list for Column K (RMO) to store the month (text), and column N is a numeric value. Any other ideas? Regarding Biff's comment for =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0))...yes, I got the same result when I copied and pasted. Even though I corrected it, it still didn't work. "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0)) Biff "Pete_UK" wrote in message roups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M***arch")*Replace ments!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
If you want to send me a copy of the file I'll take a look. I'm sure it's
something simple. I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "Jan" wrote in message ... With the non array alternative I get the #Value error. This is really strange....I thought any number of the solutions provided would have worked. More info if it will help. Column C (RBU) I have used a validation list to select Business Unit. I have used a validation list for Column K (RMO) to store the month (text), and column N is a numeric value. Any other ideas? Regarding Biff's comment for =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0))...yes, I got the same result when I copied and pasted. Even though I corrected it, it still didn't work. "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0)) Biff "Pete_UK" wrote in message oups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M**arch")*Replacem ents!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
D8 is an array formula, which once I get the name ranges to work I plan to
change the worksheet/cell ranges with the named ranges. {=C8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="February")*Replace ments!$N$3:$N$15)}. But I believe I solved my problem. I think during the course of checking my named ranges that the cell reference were not identical. The Sumproduct formula provided by Biff now works. Thank you to everyone for your help. "Pete_UK" wrote: What does D8 contain then, Jan? Pete On Feb 26, 7:45 pm, Jan wrote: With the non array alternative I get the #Value error. This is really strange....I thought any number of the solutions provided would have worked. More info if it will help. Column C (RBU) I have used a validation list to select Business Unit. I have used a validation list for Column K (RMO) to store the month (text), and column N is a numeric value. Any other ideas? Regarding Biff's comment for =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-Â*arch"),RLeas,0))...yes, I got the same result when I copied and pasted. Even though I corrected it, it still didn't work. "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-Â*arch"),RLeas,0)) Biff "Pete_UK" wrote in message roups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="MÂ*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="MÂ*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="MÂ*Â*Â*arch")*Repl acements!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use named ranges in array formula
Ah well, thanks for feeding back - glad you got it to work in the end.
Pete On Feb 26, 8:05 pm, Jan wrote: D8 is an array formula, which once I get the name ranges to work I plan to change the worksheet/cell ranges with the named ranges. {=C8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="F*ebruary")*Replac ements!$N$3:$N$15)}. But I believe I solved my problem. I think during the course of checking my named ranges that the cell reference were not identical. The Sumproduct formula provided by Biff now works. Thank you to everyone for your help. "Pete_UK" wrote: What does D8 contain then, Jan? Pete On Feb 26, 7:45 pm, Jan wrote: With the non array alternative I get the #Value error. This is really strange....I thought any number of the solutions provided would have worked. More info if it will help. Column C (RBU) I have used a validation list to select Business Unit. I have used a validation list for Column K (RMO) to store the month (text), and column N is a numeric value. Any other ideas? Regarding Biff's comment for =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0))...yes, I got the same result when I copied and pasted. Even though I corrected it, it still didn't work. "T. Valko" wrote: Toppers' array formula should work. No real need for the IF. Here's a non-array alternative: =D8+SUMPRODUCT(--(RBU="NE-777-IUC"),--(RMO="March"),RLeas) Now, here's something strange. I'm using OE to access the group. Pete is apparently using Google Groups. Pete's formula appears OK in OE: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) But, when I copy it and paste it into Excel it comes out as: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M-*arch"),RLeas,0)) Biff "Pete_UK" wrote in message roups.com... Sorry, I forgot about the named ranges. Try this: =D8+SUM(IF((RBU="NE-777-IUC")*(RMO="M*arch"),RLeas,0)) Again, entered using CSE. Hope this helps. Pete On Feb 26, 6:59 pm, "Pete_UK" wrote: Try this as an array* formula: =D8+SUM(IF((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K $15="M*arch"),Replacements!$N$3:$N$15,0)) I presume you want to add to D8 all the values from N3:N15 where C3:C15="NE-777-IUC" and K3:K15="March". * as this is an array formula, you must use CTRL-SHIFT-ENTER to commit the formula, rather than just ENTER. Hope this helps. Pete On Feb 26, 6:43 pm, Jan wrote: When I enter the formula you show, I only get the #Value error. If I enter the formula as an array: ctrl, shift, enter, I get the #N/A error. Any other suggestions? "Toppers" wrote: Try: =D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas) "Jan" wrote: I am trying to use named ranges in an array formula and it won't work. Below is the current array formula as entered in cell E8. {=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="M****arch")*Replac ements!$N$3:$N$15)} Following are the named ranges from another worksheet withing the same workbook. Name range for Column $C$3:$C15 = RBU Name range for column $K$3:$K$15=RMO Name range for Column $N$3:$K$15=RLeas How would I rewrite the array formula to use only the named ranges? Any help will be greatly appreciated. TIA Jan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using named ranges for formula abbreviations | Excel Worksheet Functions | |||
| UPDATED - Referencing named Ranges within a Nested IF formula | Excel Worksheet Functions | |||
| Help to adapt Formula syntax to work with Dynamic Named Ranges | Excel Worksheet Functions | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
| Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Linear Mode
