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 -
|