excel - Calculate cell only when column header equals a certain value -

first time poster appreciated.

i perform calculations on given row when column header contains value.

column **name** column b **test 1** column c **h1** column d **h2** column e **test 2** 

the above example better explain i'd do. have series of columns, test results , homework results. tests , homework results can in order. perform calculations such average, sum etc based on whether homework or test. example average homework results pupil (on given row).

is possible , if how. using excel 2010 if helps.

you can use sumif , countif functions. lets says worksheet looks this:

name    test 1  h1  h2  test 2   john    10      20  30  20       

then can these values:

no. of tests    no. of h    sum of test sum of h   avg of test  avg of h     2               2           30          50         15           25       

by doing this:

the sum testis calculated using =sumif(b1:e1;"test *";b2:e2) , no. of testsis calculated using =countif(b1:e1;"test *") (to number of testin header range).

the avg of...is simple division of "sum of" , "no. of" values.

of course have adjust range used match actual range of values in header , value rows.