Using data from a column with a particular heading

[codyryan]

SEE SPREADSHEET IN COMMENTS BELOW, NOT THIS ONE
Hi all, I am looking for some help with the spreadsheet below:

In the Data Analysis tab in, for example, cell B17 I have set it to search for all "pupil premium" pupils who are performing "below" where they should. The problem is that for each different group of pupils sometimes the headings on our school system are swapped (so for example the heading "Pupil Premium Indicator" may not always fall in the D column). Is there any way of getting Excel to search for that heading and only use data from that column?

I am trying to do the same for the headings "SEN Status" and "Gender" as well but if you are able to help me out with a formula for the above, I should hopefully be able to work it out.

Many thanks in advance

[codyryan]

Blank analysisHELP1.xlsx

sorry this is the correct spreadsheet ^^

[AliGW]

Will the column's heading be exactly the same, regardless of its position in the sheet?

[codyryan]

That's correct

[AZ-XL]

Hi
=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("Pupil Premium Indicator",Info!$A$1:$AO$1,0))="Y")*(Info!$AH$2:$AH$39="Below"))

For others( gender and etc) you can type or modify formula or if its written in any cell you can reference it.

[codyryan]

That's great thank you very much! I am trying to do the same for cells b22, b23 and b24 but the problem is that the indicator for SEN pupils can be either "S", "A" or "P" rather than just being "S" for example. How do I formulate it so that it can recognize all of these letters?

[AliGW]

=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$A$1:$AO$1,0))=OR("S","A","P"))*(Info!$AH$2:$AH$39="Below"))

Instead of ="Y", use =OR("S","A","P")

[AZ-XL]

Here is my solution. Paste to B22 and drga down
=SUMPRODUCT(IF(ISERROR(SEARCH(INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$A$1:$AO$1,0)),"SAP")),0,1)*(Info!$AH$2:$AH$39=$A17))

[AZ-XL]

Formula above will give you wrong result:
=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$A$1:$AO$1,0))={"S","A","P"})
*(Info!$AH$2:$AH$39=A22))
Below = 0
Expected = 2
Above = 3

But in total there is six "SAP" letters. In column AH one of the cells left blank that is why this formula in total gives 5 (2+3)

[codyryan]

Hey thanks again for that. I just tried pasting a new set of data and the formulae don't seem to be working now? Any idea what i'm doing wrong?

Here's what it looks like with the new data:
Blank AnalysisHELP2.xlsx

[AliGW]

You need to apply the INDEX and MATCH functions to the second half of the formula. In the new sheet, the range is the AG column, not AH.

[AliGW]

Try this in B17:

=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("Pupil Premium Indicator",Info!$1:$1,0))="Y")*((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))="Below")))

[codyryan]

Sorry for the late response, been working! Thanks I'll give it a try now and let you know how I get on

[codyryan]

OK the formula you gave worked great, but now i'm struggling with cells: B12, B13 and B14 and also cells B3,4,5,7,8 as I don't know how the index formula really works.

Sorry to ask so much of everyone!
Blank AnalysisHELP3.xlsx

[codyryan]

Can anyone help?

[AliGW]

Tomorrow, maybe, if nobody else has stepped in.

[AZ-XL]

Formula has given there. Only thing you should do is to change "Pupil Premium Indicator" to the required column name. In B12:B14 cells right relevant column name (ex Progress).

[AliGW]

In B12:

=COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))),"Below")

In B3:

=COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("Gender",Info!$1:$1,0))),"M")

In B7:

=COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("Pupil Premium Indicator",Info!$1:$1,0))),"Y")

In B8:

=COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))),"S")+COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))),"A")+COUNTIF((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))),"P")

How INDEX and MATCH work (and I have only recently worked this out myself by following threads like this on these boards):

MATCH: find a cell in row one on the Info sheet containing "Gender" (for example)
INDEX: carry out the calculation required on cells in the range A2 to A39 that are found below the cell found by MATCH

I hope this helps!