Only count data from column with a particular heading

[codyryan]

Hi I have had lots of help with this today, but unfortunately it's still not quite complete. I wonder if someone can help me with a formula to fill in cells B3,4,7,8,12,13,14 using something similar to the formula in cell B17 (all on the DATA ANALYSIS tab)

It has been suggested to use COUNTIF, but because I will have to paste new spreadsheets onto the info tab sometimes the titles are in different columns depending on how it was originally formatted.

See here: Blank AnalysisHELP3.xlsx

Many thanks

[Pete_UK]

So, are you saying that the Gender won't always be in column D (as the Info sheet has it now, though the formula in B3 is looking at column E) ?

Pete

[codyryan]

Yeah sometimes the gender will be further over in E or F depending on which set of data I paste in. Same goes for the other titles in row A, they all swap about

[Pete_UK]

Well, that doesn't sound like a very consistent design approach on the data layout !!

Here's how you can get the number of males in B3:

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

and the number of Females in B4:

=SUMPRODUCT(--(INDEX(Info!$A$2:$AO$39,0,MATCH("Gender",Info!$1:$1,0))="F"))

I've shown in red the things that have changed - can you see how to do this for your other cells?

Pete

[codyryan]

Ah yes that's great, it's working! The only problem i'm having now is that for the "number of SEN" in B8, the SEN pupils are indicated by either A S or P and not just by one thing (like M for male) so i'm not sure how to get it to look for any of these letters in the info tab under SEN status

[codyryan]

PS yep the design is appalling! haha but i'm trying to get it so that my colleagues can paste in their own gathered data into the Info tab so that the Data analysis tab just works it out for them

[Pete_UK]

You also have N in that column (and a few blanks) - what does that signify?

Pete

[codyryan]

The N can be ignored as it means that pupil is not counted as SEN (special educational needs)

[Pete_UK]

Okay, sometimes it can be easier to write the formula so that it counts the items that are not equal to the non-valid codes, but as you also have blanks in there it makes little difference.

The formula you currently have is essentially this:

=COUNTIF(B2:B32,"A")+COUNTIF(B2:B32,"P")+COUNTIF(B2:B32,"S")

(I've removed the sheet references). It's a bit long-winded, but you could use a similar approach with the longer SP formula, adding three of them together, each looking in the SEN Status column for A P and S in turn. However, here's a more compact version of the COUNTIF formula:

=SUM(COUNTIF(B2:B32,{"A","P","S"}))

Note that you can put the 3 variables within curly braces, each separated by a comma - a SUM function is required around the formula to essentially add the 3 different numbers that the COUNTIF produces, though this is not required for the SP version.

So, change the column header from Gender to SEN Status, and use {"A","P","S"} instead of the "M" in the formula I gave you for the number of males.

Note that I'm not giving you this "on a plate" - you have to work for it, and then you might learn more from it (as I'm sure you tell your pupils).

Hope this helps.

Pete

[codyryan]

You're an absolute star!

Going from what you have said I actually ended up using
=SUMPRODUCT(--(INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"A","S","P"}))

I wasn't aware of the curly brackets. You have been a really great help!

I'm sure I will be back mithering again shortly though haha

[codyryan]

OK my absolute last:

I tried to type this into B25
=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"S,"A","P"})*((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))="Below")))

to find out how many SEN pupils are performing "below" what they should be, but apparently my formula contains an error...

[codyryan]

Ah got it, I changed to

=SUMPRODUCT((INDEX(Info!$A$2:$AO$39,0,MATCH("SEN Status",Info!$1:$1,0))={"A","S","P"})*((INDEX(Info!$A$2:$AO$39,0,MATCH("KS2-3 Progress4 Y7 Spring 2",Info!$1:$1,0))="Above")))

[Pete_UK]

Well, that's great. I'm sure you realise that the long INDEX...MATCH terms are just selecting the columns to use from the headings. With this longer formula you have two terms separated by an asterisk - essentially, that is equivalent to having two terms which are ANDed together, i.e. both of them have to be satisfied for any given row for it to be counted. I bet you can work out what to do if you have three conditions ...

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete