Averaging COUNTIF

[Wynn]

I want to average numbers in column B, only in column A has a specific term
(in other words, an average of all rates in column B where column A contains
the word "DG").

I am figure I need to use COUNTIF, OFFSET, and AVERAGE functions, but I am
having trouble putting them together. Thanks!

 

[JulieD]

Hi Wynn

only SUMIF and COUNTIF

=SUMIF(A1:A10,"DG",B1:B10)/COUNTIF(A1:A10,"DG")

Cheers
JulieD

 

[Frank Kabel]

Hi
try the following array formula
=AVERAGE(IF(A1:A100="DG",B1:B100))

 

[Wynn]

Frank,
Thank you. This is working very well. I am having a problem however,
because I want to test values for two columns and it does not seem to be
working with the AND function. Am I missing something?:

array formula
=AVERAGE(IF(AND(A1:A100="DG",C1:C100="BG"),B1:B100))

 

[Frank Kabel]

Hi
for some general stuff about this see:
http://www.dicks-blog.com/archives/2004/12/04/logical-operations-in-arr
ay-formulas/trackback/

In your case try:
=AVERAGE(IF((A1:A100="DG")*(C1:C100="BG"),B1:B100))

 

[Wynn]

Thanks, Frank - you always come through for me. Thanks for the site as well.