Use named ranges in array formula

[Jan]

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")*Replacements!$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

 

[Toppers]

Try:

=D8+SUM((RBU="NE-777-IUC")*(RMO="March")*Rleas)

 

[Jan]

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?

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Jan]

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]

Does the range RMO actually contain month names, or are these dates?

Pete

 

[T. Valko]

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

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

 

[Jan]

The month name is text.

Does the range RMO actually contain month names, or are these dates?

Pete

 

[Pete_UK]

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

 

[Jan]

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.

 

[Pete_UK]

What does D8 contain then, Jan?

Pete

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.







- Show quoted text -

 

[T. Valko]

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]

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")*Replacements!$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]

Ah well, thanks for feeding back - glad you got it to work in the end.

Pete