Honor roll calculation - add subject grades based on subject list + ignore some subjects

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks, I have added the attachment!

With helper in D

in D2

=IF($C2<>MAX(IF($B$2:$B$8=$B2,$C$2:$C$8)),"",$C2)

Copy down

in D9

=SUM(D3:D8)

Not the expected result - but thank you for trying!
It only produces the MAX grade of the subject with the highest grade...

Help is still needed.

WOW! Thank you!

Is it possible to modify it so that the text can be slightly different? E.g. text that cointains "lang & Lit" maybe using *?

Are "Lang & Lit" only courses where duplicates occur?

No. I will have to do the same for "Lang Acq".

So why not this ...

NOTE: formula is array formula ..

...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.

That would be great but the text before "Lang & Lit" and/or "Lang Acq" may change according to the language the student studies. e.g. French Lang & Lit, English Lang & Lit, Spanish Lang & Lit.
If it could point to a preset list of possible subjects it would be OK. I could make the list of all options but I think that that is more complex than adapting the formula to:

=IF($C2<>MAX(IF($B$2:$B$8=$B2,$C$2:$C$8)),"",$C2) (ARRAY) -> Where the "=$B"" would be something that contains "Lang & Lit". does that make sense?

I am confused as to why you cannot simply use the "helper" column approach which does not rely on the order of, or content of , column B..

Column B can contain any text.

Perhaps post another file illustrating the "problem"..

Hi,
Thanks for all the help.

I like the helper column but the thing is that a student may have English Lang & Lit with French Lang & Lit where we only count the best grade of these two added the the grades of the other subjects.

Thanks!

Yes ... I eventually realised what you meant, In this case it would necessary to use the "wildcard" as you indicated earlier.

I'll take a look at this.

That would be great. Look forward to hearing from you.

Long formula ..which hopefully you can adapt


=IF(RIGHT($B2,10)="Lang & Lit",IF($C2<>MAX(IF(RIGHT($B$2:$B$10,10)="Lang & Lit",$C$2:$C$10)),"",$C2),IF(RIGHT($B2,8)="Lang Acq",IF($C2<>MAX(IF(RIGHT($B$2:$B$10,8)="Lang Acq",$C$2:$C$10)),"",$C2),IF($C2<>MAX(IF($B$2:$B$10=$B2,$C$2:$C$10)),"",$C2)))

...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.

FANTASTIC!!!!!!
Thank you very much! Have a great weekend!

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