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!

 

[Aladin Akyurek]

What are the expected values as it's not clear how 3 for read, 3 for blue, 2.4 for green, etc. obtain?

 

[offthelip]

Assuming the first column is column A try this:
=SUMIF($F2:$F6,A1,A2:A6)+SUMIF($G2:$G6,A1,A2:A6)+SUMIF($H2:$H6,A1,A2:A6)/(COUNTIF($F2:$F6,A1)+COUNTIF($G2:$G6,A1)+COUNTIF($H2:$H6,A1))

 

[exceldata]

Thank you, offthelip. The column with header Red is column B. So I took your formula and changed all of the A's to B's, and changed F,G,H into G, H, I. But it gave me this answer: 6.333.

The correct answer should be 2.333, so I'm not sure what needs to be changed.

For some clarification, Aladin:

The 7th row down is marked "Average B2:B6", and it has averages for the five numbers above it. I want to calculate that basic average, but then in the next cell below it I want to calculate the average of only numbers that appear in a row that also contains the color text that matches the text in the column header. So in column B (Red) I want it to average 2,4, and 1, because the word "Red" appears in their rows off to the right.


Thanks for your input!

 

[Aladin Akyurek]

In B8 control+shift+enter and copy across:

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

 

[exceldata]

This works - thank you so much!!

 

[exceldata]

Could this be easily amended to calculate an average using the opposite criteria - only numbers that do NOT have the column header word appearing in their row?

 

[Aladin Akyurek]

Could this be easily amended to calculate an average using the opposite criteria - only numbers that do NOT have the column header word appearing in their row?

Control+shift+enter, not just enter, and copy across:

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

 

[exceldata]

This worked perfectly. Thank you so much!! Your help is greatly appreciated!

 

[Aladin Akyurek]

This worked perfectly. Thank you so much!! Your help is greatly appreciated!

You are welcome.