Sumproduct Excluding Data
Perfect!!
Thanks Shane!
"Shane Devenshire" wrote:
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
"Brian" wrote:
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<ISNUMBE R(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<ISNUMBE R(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
|