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

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


Thank you!

… also look at AVERAGEIF and AVERAGEIFS.

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

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