Sumproduct Excluding Data

[Brian]

Is there a way to setup a formula using Sumproduct to exclude data?
Specifically, I'm trying to calculate a weighted average using the following
formula...

=SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<>ISNUMBER(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200,CE!$P$2:$P$200)/SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<>ISNUMBER(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200)

The question is how do I re-write the term
(CE!$F$2:$F$200<>ISNUMBER(SEARCH("gov",CE!$F$2:$F$200)))
so that the calculation does not include any data that has the word "gov" in
column F

Just for clarity....Column G represents Money Market Funds...Column Z is the
investment amount....and Column P is the interest rate.

Thank you

 

[Shane Devenshire]

Hi,

You could try something like this

=ISERR(SEARCH("gov",G5:G11))

This returns TRUE for all cells not containing gov. Adjust for your range.

 

[Brian]

Perfect!!
Thanks Shane!

Hi,

You could try something like this

=ISERR(SEARCH("gov",G5:G11))

This returns TRUE for all cells not containing gov. Adjust for your range.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire