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 -