Total of marks with the specific conditions

[nskatoch]

R.No.	NAME OF STUDENT	ENGLISH	HINDI	PUNJABI	MATHS.	SCIENCE	S.SCI.	GERMAN	SKILL	Total
Maximum Marks: -	100	100	100	100	100	100	100	100
1	ARYAN	45	56	66	32	78	44	43	42
2	BALLU	50	45	65	45	12	55	42	40
3	CRAYAN	67	67	63	66	45	3	41	44
4	DOLLY	33	78	64	77	67	78	80	81
5	ELISHA	78	79	61	88	64	63	55	90
6	FLOWER	46	92	62	98	45	44	60	88
7	GAUTAM	90	50	60	87	50	89	48	57
8	HAPPY	55	67	65	76	76	56	60	68​

In the above table, I want the formula for TOTAL, which fulfill the following conditions: -

• The pass criteria is 33 marks.
• If the marks of Maths or Science or S. Sci. Less than 33 and the marks of Skill is more than 33, then marks of Maths/Science/S.Sci replaced by skill subject. (e.g. Maths-23, Science-45, SSci-56 and Skill-78 then Skill replaced the Maths.)
• If the marks of Hindi or Punjabi less than 33 and German has more then 33 marks then marks of Punjabi/Hindi replaced by German. (e.g. Hindi-32, Punjabi-80, German-47 then German replaced the Hindi)

 

[nskatoch]

I seem to have misunderstood your query. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	R.No.	NAME OF STUDENT	ENGLISH	HINDI	PUNJABI	MATHS.	SCIENCE	S.SCI.	GERMAN	SKILL	Total
3	Maximum Marks: -	100	100	100	100	100	100	100	100	100
4	1	ARYAN	45	56	66	32	78	44	43	42	331
5	2	BALLU	50	45	65	45	12	55	42	40	300
6	3	CRAYAN	67	67	63	66	45	3	41	44	352
7	4	DOLLY	33	78	64	77	67	78	80	81	397
8	5	ELISHA	78	79	61	88	64	63	55	90	433
9	6	FLOWER	46	92	62	98	45	44	60	88	387
10	7	GAUTAM	90	50	60	87	50	89	48	57	426
11	8	HAPPY	55	67	17	25	25	56	60	11	288
Sheet8

Cell Formulas
Range	Formula
K4:K11	K4	=SUM(C4:H4)+IF(AND(MIN(D4:E4)<33,I4>=33),I4-MIN(D4:E4))+IF(AND(MIN(F4:H4)<33,J4>=33),J4-MIN(F4:H4))

Thanks.

 

[Eric W]

Glad we could help.