# Only count data from column with a particular heading

1. ## 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.

See here: Blank AnalysisHELP3.xlsx

Many thanks

2. ## Re: Only count data from column with a particular heading

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

3. ## 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

4. ## Re: Only count data from column with a particular heading

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

5. ## 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

6. ## 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

7. ## Re: Only count data from column with a particular heading

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

Pete

8. ## Re: Only count data from column with a particular heading

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

9. ## 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:

=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

10. ## Re: Only count data from column with a particular heading

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

11. ## 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...

12. ## Re: Only count data from column with a particular heading

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")))

13. ## 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 ...

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1