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 -