solve if you can

[ubaid]

Dear All,

sum both session fall and spring but the score of subject english value will taken once which is max value,whether we change put subject "english location to any cell to any it will take english max value and sum with other,

if you can please suggest me it urgent.iam upload sample file.

 

[Hui]

I think this is what your after:
=SUMPRODUCT(((C4:C15<>"English")*(C4:C15<>""))*(G4:G15))+SUMPRODUCT(MAX((C4:C15="English")*(G4:G15)))

 

[Xiq]

Hi Ubaid,

I'm sorry, I have no clue what you want

Can you give a manual example of the results you are looking for?

 

[Hui]

I hope he wants the sum of all the results excluding English + the maximum English result

 

[ubaid]

thanks buddy it working but i could not understand the basics of formulas can you elaborate it for my learning.

 

[Luke M]

=SUMPRODUCT(((C4:C15<>"English")*(C4:C15<>""))*(G4:G15))+

This part builds 3 arrays. The first two will be true/false arrays based on criteria, the third G4:G15 has numbers. When you multiply the arrays against each other, any row where a false occurs will evaluate to 0, and thus not get added to the total sum.

SUMPRODUCT(MAX((C4:C15="English")*(G4:G15)))

For the 2nd part, we're taking just 2 arrays, and only 1 of them is a true/false array. This will produce a single array of all the numbers in G4:G15 where C4:C15 was the word English. The MAX function then pulls out a single number from this array. The SUMPRODUCT function is only used so that we can handle arrays naturally, w/o having to confirm formulas as an array.

 

[ubaid]

Dear Hui,

Iam attaching the sample which was solved you yesterday,but dear now i want that if any subject repeat it will take maximum value(we dont need to specify any one subject).if this is possible kinldy send me the solution.


Regards
obaid amazai