# EXCEL Cell content based on content in other cell (same row)

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

In Cell `C8` write this formula & fill down:
=`SUMIFS(\$B\$2:\$B\$6,\$A\$2:\$A\$6,B8,\$C\$2:\$C\$6,A8)`
In Cell `C12` write this formula & fill down:
`````` =AVERAGEIF(\$A\$8:\$A\$10,A12,\$C\$8:\$C\$10)