Counting using a formula

[Doofus1]

I need to calculate these figures in the order described on the excel spreadsheet.
However, true to my user name, I need help creating this formula.
Any help will be greatly appreciated.
Please check the Excel spreadsheet for the explanation of the problem.

[SlipEternal]

I have no clue what goes in column F.
Cell G4 would just be:
=COUNTIF(C:C,C4)

[Doofus1]

Column F would show a count of the number of times the student topped his/her previous top score - stating with the first time the student scored a 50 or above. So for example, John did that 4 times and Mary did that 7 times. But thanks for the COUNTIF suggestion.

[SlipEternal]

Column F would show a count of the number of times the student topped his/her previous top score - stating with the first time the student scored a 50 or above. So for example, John did that 4 times and Mary did that 7 times. But thanks for the COUNTIF suggestion.

Is it going from bottom to top?

[Doofus1]

From the bottom to the top.

[SlipEternal]

Here you go. I used helper columns.

[Doofus1]

You are a genius!!! Thank you!!!

[SlipEternal]

No problem. If a solution is provided, please mark the thread as solved.

[Doofus1]

SlipEternal,
Question for you on what you solved for me.
How can the formulas in columns, A, B and E be put together so that I can copy and paste the formula's in columns F and G (without also, having to copy the formula's in column A, B and E also)?
Any suggestions?

[JohnTopley]

Try this:

Helper in Column C

in C4

=IF(B4<MAX(IF($A4:$A$100=A4,($B4:$B$100)*($B4:$B$100>=50))),0,MAX(IF($A4:$A$100=A4,($B4:$B$100)*($B4:$B$100>=50))))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.


Then copy down

[samba_ravi]

see the attached file
use below formula in F4 and copy towards down

Please Login or Register  to view this content.

the formula is with Subtotal and Offset Function which is a volatile function, which is not suggestible.
Use helper column as suggested by Jhon which is simple to understand and easy to modify

[Doofus1]

Thanks again Mr Topley!!
This looks cleaner.

[Doofus1]

Dear Samba,
Thank you will test this formula out too.
You guys are all a great bunch of teachers.
Thank you

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Doofus1]

Hi Samba or anyone else who feels like offering a suggestion,
Do you know of how to write a formula that (1) identifies the individual student's highest score and then, (2) display it in the top-most cell, for each student, in column E- like in this example?

I want to be able to copy and paste the formula down that column (without having to copy additional columns).