# If cells contain M then only look at them

1. ## If cells contain M then only look at them

Hi all,

I am trying to make some analysis a little easier and wondered if you could help. On the "Analysis" tab I would like to be able to show a number and a percentage for each of the categories in column A.

So for example column E3 should work out that the number of males in the class (those who have "M" in column B on Sheet1 who are "Above Target" is 0 and 0%.

As I grow the spreadsheet I will also have a "Below" section. So for example LAP students on the Analysis tab who are "Below" target will show as 4 students and 36%

I just need help with the formula so I can grow the spreadsheet from there.

2. ## Re: If cells contain M then only look at them

Sorry I should add that I am referring to column AB on Sheet1 for the Above, on, below and sig below targets

3. ## Re: If cells contain M then only look at them

*Edit - ignore this, it didn't work

Ah sorry, worked it out
=IF(COUNTIF(Sheet1!B2:B35,"m"),COUNTIF(Sheet1!AB2:AB36,"Above"))

4. ## Re: If cells contain M then only look at them

Perhaps a Pivot Table

 v A B C D 3 Gender Abililty Group :Key Stage 2 Data Count of EAL Count of Gender 4 F 5 HAP 1 1 6 LAP 2 6 7 MAP 4 8 (blank) 1 2 9 F Total 4 13 10 M 11 HAP 1 12 LAP 5 13 MAP 1 4 14 (blank) 1 3 15 M Total 2 13

5. ## Re: If cells contain M then only look at them

Originally Posted by codyryan
Ah sorry, worked it out
=IF(COUNTIF(Sheet1!B2:B35,"m"),COUNTIF(Sheet1!AB2:AB36,"Above"))
Actually my way didn't work, for some reason it is giving me the answer 6 whether I put in M of F

6. ## Re: If cells contain M then only look at them

Many thanks for your help Alan, I like the way that looks, but I really don't understand pivot tables. Is there a way to make them show all of the following categories and the breakdown of Above, On, Below and Sig below target? along with percentages?

Categories:
Males
Females
HAP
MAP
LAP
EAL
PP
SEN

Thanks

7. ## Re: If cells contain M then only look at them

Turns out this was what I was looking for:

=COUNTIFS(Sheet1!B2:B35,"m",Sheet1!AB2:AB35,"on")

8. ## Re: If cells contain M then only look at them

Thanks for letting us know.

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