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

#### sebbo6

##### New Member
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Chris Mack

##### Well-known Member
Code:
``=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

##### New Member
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.

#### Chris Mack

##### Well-known Member
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

##### New Member
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

##### Well-known Member

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

Chris.

#### sebbo6

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

Yw!

Replies
0
Views
1K
Replies
2
Views
2K
Replies
9
Views
3K
Replies
2
Views
2K
Replies
11
Views
1K

1,195,936
Messages
6,012,394
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back