How to calculate an average using only certain rows of data, based on whether a text word in the column header appears in any cell along that row?

[exceldata]

Hello,

I would like to use a formula to calculate the Average of the numbers in the first column of data (Column Header:"Red"), but I only want to use the data in cells that are in a row that contains the Header text somewhere in that row. So for Column Header Red, I would like to use the first number, 2, the second number, 4, skip the third number, 5, use the forth number, 1, and skip the last number, 3.

I would like to enter this formula along the row where I have the text showing in a red color in this post.

Could anyone help me with this? It would be greatly appreciated!

[TABLE="width: 860"]
<tbody>[TR="bgcolor: transparent"]
[TD][/TD]
[TD]Red[/TD]
[TD]Blue[/TD]
[TD]Green[/TD]
[TD]Yellow[/TD]
[TD]Orange[/TD]
[TD]Color Choice 1[/TD]
[TD]Color Choice 2[/TD]
[TD]Color Choice 3[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Respondent 1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Respondent 2[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent"]None[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Respondent 3[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Respondent 4[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Orange[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent"]Red[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Respondent 5[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent"]None[/TD]
[TD="bgcolor: transparent"]Purple[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Average B2:B6[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2.4[/TD]
[TD="bgcolor: transparent"]3.2[/TD]
[TD="bgcolor: transparent"]3.4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Average for all Respondents who chose the Column Header Color in any Column Choice Option, G through I



I apologize in advance for the appearance of this table. I read the instructions and downloaded the tools but could not figure out how to generate a table that would post properly in this comment box.[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[exceldata]

Actually, I'm sorry to bug you again, but I could use help amending both of these equations that you developed. I realized that my actual spreadsheet has blank cells, which are being counted and lowering the averages I'm calculating. Can these be fixed so that they do not count the empty/blank cells as zeros?

 

[Aladin Akyurek]

Actually, I'm sorry to bug you again, but I could use help amending both of these equations that you developed. I realized that my actual spreadsheet has blank cells, which are being counted and lowering the averages I'm calculating. Can these be fixed so that they do not count the empty/blank cells as zeros?

Control+shift+enter…

[1]
=AVERAGE(IF($G$2:$I$6=B$1,IF(ISNUMBER(B$2:B$6),B$2:B$6)))

[2]
=AVERAGE(IF(IF(MMULT(($G$2:$I$6=B$1)+0,TRANSPOSE(COLUMN($G$2:$I$6)^0)),0,1),IF(ISNUMBER(B$2:B$6),B$2:B$6)))

 

[exceldata]

Fantastic again. You're the best!

 

[Aladin Akyurek]

Fantastic again. You're the best!

You are welcome.