# Compute total score based on student number

1. ## Compute total score based on student number

Hi guys/gals,

I want to compute the [Total] and [Number of Attempts] each student received based on three separate tests.

Here is a sample:

[Student Number] [Score]
1299375 2
1299375 1
1299375 1
1300508 1
1300508 1
1300607 0
1300904 0

I would like the output to look like this:

[Student Number] [Total] [Attempts]
1299375 4 3
1300508 2 2
1300607 0 1
1300904 0 1

Have also attached full file I want to manipulate.

Thanks for the help!

2. ## Re: Compute total score based on student number

Amended file attached, with three formulae giving what you want.

Hope this helps.

Pete

3. ## Re: Compute total score based on student number

Thanks Pete.

I would have never solved it myself.

Looking at the function now so that I can fully understand it.

Edit: I can't work out how to mark this thread as [solved].

4. ## Re: Compute total score based on student number

Sinth: Read my signature to mark it as SOLVED

5. ## Re: Compute total score based on student number

Thanks kelshaer.

Actually, I have one more question.

Why am I unable to modify the formula.

When I simply click on the formula and then click elsewhere, it returns the result "n/a"

It appears that the brackets go missing and then it stuffs up. The brackets are { and }

I want to change the range on the formula but can't do this!

It's something to do with the countif function but I can't work out exactly what!

6. ## Re: Compute total score based on student number

Originally Posted by sinth
Thanks kelshaer.

Actually, I have one more question.

Why am I unable to modify the formula.

When I simply click on the formula and then click elsewhere, it returns the result "n/a"

It appears that the brackets go missing and then it stuffs up. The brackets are { and }

I want to change the range on the formula but can't do this!

It's something to do with the countif function but I can't work out exactly what!
Hi sinth,

it's because the first formula is an array formula. When you type it in (or edit it in your case) you must commit it using the key combination of Ctrl-Shift-Enter rather than the usual <enter>. If you do this correctly then Excel will wrap curly braces { } around the formula, but you must not type these yourself. You can then copy the formula down the column in your usual way.

Hope this helps.

Pete

7. ## Re: Compute total score based on student number

Yes, it does help.

I learnt that the hard way, heh I was trying to put the brackets in myself!

Thanks very much again!

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