Search for cell value and then do countif based on column found

[neilos81]

Hi

Sorry to post again so soon, having further issues with my task. I know there is a very manual long-winded way around this but that's not really what Excel is about so, here goes.

I want to do a very straightforward countif. Well actually, 30 countifs on 30 different columns that arent in the same order and might be a totally different place next time I paste the raw data in.
Is there any way to search for a cell value in a row of headers, and then use the result of that to put into the column criteria for a countif? Setting it up 30 times with the possibility of having to redo many times over is slightly concerning.

Current countif looks like =COUNTIF(sheet2!$B$1:$B$1000,C3) and will be dragged to repeat for D3, E3, etc. sheet2 Column B holds grade info for a specific subject and counts the number of specified grades - C3 = "A", D3 = "B" etc

I need to do something similar for each of the 30 subjects (downwards), so i would like to search for the subject (stored in B3 and downwards in column B) in row 2 of sheet2, and then use the resulting column reference in the countif.

Hope that makes sense...

Many thanks in advance for any guidance

 

[GlennUK]

Yes, use SUMPRODUCT ... like =SUMPRODUCT(--(sheet2!$B$2:$ZZ$2=subject_cell_ref)*--(sheet2!$B$3:$ZZ$1000=C3)) - as a guess. Change the subject_cell_ref to be the reference of the cell with the required subject.