Get the average sum of column if the value in another column has a certain keyword

[dzac]

Hi all, I am trying to get the average of a selection of a column only if the value in another column fits the criteria.

For example, I have Column B that showcases a number of hours.

Column B

• 1.0
• 2.0
• 3.0
• 1.5

And I have a Column J that showcases the gender

Column J

• M
• M
• F
• M
• F

I had wanted to get the average sum of hours (column B) only if the value in the gender (column J) is M.
In my example, it will be (1+3)/3 = 1.33, since B2 is blank

While I can do this manually, however I have a huge data set…
Pardon me for not illustrating it in a better manner as I could not find a way how to represent it here…

Appreciate in advance for any replies

[JohnSUN]

=SUMIF(J1:J5;"M";B1:B5)/COUNTIF(J1:J5;"M")

[dzac]

Thank you!

[mikekaganski]

… also look at AVERAGEIF and AVERAGEIFS.

[JohnSUN]

No, I tried this. It will 2.00, but not 1.333 - an empty cell will not be considered in the denominator

[mikekaganski]

@JohnSUN: you are absolutely right - I had overlooked that part of the question.