Trying to set up what should be a simple tracker but just can't

[sebbo6]

So I am trying to set up a simple tracker for a school set of information.

I have the following columns:
Children's names, gender, SEN, EAL, FSM, PP and VA guide.

I'm trying to cross reference some of these with the VA guide (which is a score around the 100 mark. [I will basically be dealing with less than 100, 100 and greater than 100]).

So the problem I am having is that I am trying to count the EAL children (who are de-marked by 1 in the column) who get less than 100, 100 or more than 100.

EAL column is D
VA guide is M

and EAL are not sorted together as the children are sorted by children's names.

 

[Chris Mack]

=SUM(IF($D$2:$D$1000=1,IF($M$2:$M$1000>100,1,0)))

Entered with CTRL+SHIFT+ENTER; ranges can be altered to suit.

Etc.

Hope this helps,

Chris.

 

[sebbo6]

Sorry that did work perfectly but I didn't explain myself well.

Where a child is EAL (so 1 in column D) I need to count if their VA score is less than 100, 100 or more than 100.

I used the code:

=COUNTIF(M9:M37,"<100")

for the whole class (column M is the VA score).

So what I am hoping to achieve is a simple way of counting the children who are EAL and have a VA score of >100, =100, or <100.

Thank you for your help.

 

[Chris Mack]

My formula should work for that; it basically counts the number of rows with 1 in column D and >100 in column M.

Chris.

 

[sebbo6]

ADVERTISEMENT

Thanks for your help again, however I only seem to be able to get the result of 0 on all three counts (less than 100, at 100, greater than 100) which is not true of the data. Any idea why this may be happening? Thank you and sorry for being so utterly useless this is my first time trying to anything like this.

 

[Chris Mack]

Don't worry about it.

One thing to make sure of is that you're using CTRL+SHIFT+ENTER when entering the formula?

Chris.

 

[sebbo6]

Sorry I must totally have missed that! It has all been sorted now.
Thank you so much

 

[Chris Mack]

Yw!