Using Defined Names in a Formula

[Mike@Scott]

Hi Guys,

I'm trying to use a named Range in a Sumproduct Formula however what i want
to do is return the total of all the cells which contain values not in the
range.

So similiar to this Formula :

=SUMPRODUCT(--(FEB!$H$2:$H$311<>STDGL),--(FEB!$I$2:$I$311=STD),--(FEB!$F$2:$F$311))

But I need to alter it so it only sums the cells F2:F311 that dont contain
the values in STDGL for cells H2:H311 or in STD for cells I2:I311

that make sense ?

Thanks Guys

Michael -

 

[Chip Pearson]

If you want to sum all the numbers in B1:B10 that are not present in a
range named TheName, use the following formula.

=SUMPRODUCT(--(ISERROR(MATCH(B1:B10,TheName,0))),--B1:B10)

The MATCH function looks up each value in B1:B10 in the range TheName.
If that value is not found in TheName, MATCH returns an error, so the
formula looks sums only those values that cause MATCH to return an
error, that is, those that are not in TheName.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com