Only count data from column with a particular heading
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.
Re: Only count data from column with a particular heading
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
Re: Only count data from column with a particular heading
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
Re: Only count data from column with a particular heading
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
Re: Only count data from column with a particular heading
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:
(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).
Re: Only count data from column with a particular heading
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...
Re: Only count data from column with a particular heading
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).
Bookmarks