Average Column IF Column Header Has Specific Text

[jgray]

Hi.

Okay. I have a spreadsheet that is an array, with Column and Row Headers, and data. I need a formula which searches across the column headers, finds a column header with specific text, and then averages all data in the column that is >0. I have attached an example sheet so you can see what I am trying to accomplish. I have a feeling there is an easy solution, but I just can't seem to get my head to work right today.

JacobColumn Average Example.xlsx

[dilipandey]

Hi Jgray,


You can use the below formula :-

=AVERAGE(IF(data<>0,data,""))

Where data is a defined name with formula:-
=OFFSET(Sheet1!$A$2:$A$16,0,MATCH(Sheet1!D$22,Sheet1!$B$1:$L$1,0))

drag the formula to right side to achieve the desired results to match up with headings.

sample file.

Column Average Example.xlsx
regards,
DILIPandey

<click on below 'star' if this helps>

[uduyt]

use the averageif formula with the if formula, so like:
=IF(A1=C22;AVERAGEIF(A2:A16;">0");IF(B1=C22;AVERAGEIF(B2:B16;">0");""))
and so on
It would be much easier with a code though, do you have any experience with them?

[Haseeb Avarakkan]

Hi Jacob,

In C24, then copy across.

Please Login or Register  to view this content.

[jgray]

Great. These both work perfectly. Thanks!