I've got a sheet of about 500 entries. This sheet contains 4-letter words in one column, a corresponding number to this row, and a corresponding gender. I need to make a function that finds all rows with the same 4-letter word, then gets a number for males, as well as a number for females. These numbers will then be calculated to an average for males, and a average for females.

Any suggestions? :)

Doesn't have to be one formula for both genders; it can (actually would prefer it to be) one formula for each gender.

  • Most likely you need COUNTIFS function, however it's difficult to tell anything more without knowing your sample data. – Máté Juhász Aug 7 '18 at 7:42
  • The data is 500 rows, sorted as following: Score (number) : Class (about 20 different options here) : Gender (Male/Female). I need an output as following: A number, the average, for a certain class and gender. Class and gender I can set manually in each code. So basically a "if column x, row 1 = class x, then if column x row 3 = gender male ----> adds number to list that later is calculated into an average score" – Elias G Aug 7 '18 at 7:50
  • Create pivot table (using pivot table master-builder) with "4-letter words" as rows, "genders" as columns and AVG("numbers") as values. It seems that's all you need. – Akina Aug 7 '18 at 8:54
  • This is not sample data, we can't present a solution based on that. Did you tried COUNTIFS as suggested? – Máté Juhász Aug 7 '18 at 10:54
  • Solved it in another way! Probably not efficient, but it worked (y) Thanks anyways! For future reference: Used sorting, then just sumproduct for the interval, after which i divided with the size of the interval for an average. Sorry that I couldn't provide Sample Data, reason for that is that it isn't something I'm allowed to share. – Elias G Aug 8 '18 at 7:56

enter image description here

In Cell C8 write this formula & fill down:


In Cell C12 write this formula & fill down:


Note, adjust cell references in formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.