Sumifs and countif with blanks

[Dave]

C D
1 Michael,Bob 9
2 Dave, Michael
3 Todd, Sam, Bob 5


=SUMIF($C$2:$F$50,"*michael*",$D$2:$D$50)/COUNTIF
($C$2:$C$50,"*michael*")

In this formulae the calculation becomes 9+0/2= 4.5

I want to exclude zeros and blanks from being added. What
do I add to the formula to exclude those cells?

Thanks!

 

[Frank Kabel]

Hi
try the following formula
=SUMPRODUCT(--(ISNUMBER(SEARCH("michael",$C$2:$F$50))),--($C$2:$F$50<>"
"),--($C$2:$F$50<>0),$D$2:$D$50)/=SUMPRODUCT(--(ISNUMBER(SEARCH("michae
l",$C$2:$F$50))),--($C$2:$F$50<>""),--($C$2:$F$50<>0))

 

[RagDyeR]

Try this:

=SUMIF(C2:C50,"*Michael*",D250)/SUMPRODUCT((ISNUMBER(SEARCH("Michael",C2:C
50)))*(D250>0))

I'm assuming you have some typos in your question, and this excludes
negatives.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


C D
1 Michael,Bob 9
2 Dave, Michael
3 Todd, Sam, Bob 5


=SUMIF($C$2:$F$50,"*michael*",$D$2:$D$50)/COUNTIF
($C$2:$C$50,"*michael*")

In this formulae the calculation becomes 9+0/2= 4.5

I want to exclude zeros and blanks from being added. What
do I add to the formula to exclude those cells?

Thanks!

 

[Frank Kabel]

Hi
sorry, messes the range refeences. Make this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("michael",$C$2:$C$50))),--($D$2:$D$50<>"
"),--($D$2:$D$50<>0),$D$2:$D$50)/=SUMPRODUCT(--(ISNUMBER(SEARCH("michae
l",$C$2:$C$50))),--($D$2:$D$50<>""),--($D$2:$D$50<>0))

--
Regards
Frank Kabel
Frankfurt, Germany