averaging text occurences based on two criteria

[CaveGirl]

I need to average the number of "Yes" occurances in P9:P500 based on whether B9:B500 = "SA". I am totally stumped. It seems like it should be easy but for some reason I can't get my head around it today and I'm getting really frustrated!!!! Any help would be greatly appreciated. Thanks!

 

[njimack]

Not sure how you average text strings. You can count when both conditions are true:
=SUMPRODUCT(--(B9:B500="SA"),--(P9:P500="Yes"))

 

[Greg Truby]

Welcome to the board.

You neglect to state which version of Excel you are using. The following formula would work for Excel 2007 or 2010. Take a look at the COUNTIFS and COUNTIF functions. Also, the form in which you stated your problem is a bit tough to interpret. I am interpreting it to mean "# of Yeses for SA / # of SA's". Applied to your problem, I believe your solution would be:

=COUNTIFS(P9:P500,"Yes",B9:B500,"SA")/COUNTIF(B9:B500,"SA")

Let us know if you would need a version for 2003 or lower.

 

[CaveGirl]

Thanks to both of you, I am using 2007 so the first solution works perfectly, I just need to modify it to divide by the total number of entries in the P column and I have my average!

Thanks so much for the quick response, I'm so glad I found this board