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)

 

[Cubist]

What if multiple subjects are below 33? Do all of them get replaced or just one of them?
e.g. Maths-23, Science-23, SSci-23 and Skill-78

 

[Eric W]

And with Cubist's question, do you count the German and Skill categories if all the other categories are over 33? Should the result of row 5 be 6 or 8?

 

[nskatoch]

What if multiple subjects are below 33? Do all of them get replaced or just one of them?
e.g. Maths-23, Science-23, SSci-23 and Skill-78

Only anyone subject get replaced

 

[nskatoch]

And with Cubist's question, do you count the German and Skill categories if all the other categories are over 33? Should the result of row 5 be 6 or 8?

No, only main subjects count (i.e. English, Hindi, Punjabi, Maths, Science, S.Sci).

 

[Eric W]

One way (I changed some values in the last row for testing):

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	6
5	2	BALLU	50	45	65	45	12	55	42	40	6
6	3	CRAYAN	67	67	63	66	45	3	41	44	6
7	4	DOLLY	33	78	64	77	67	78	80	81	6
8	5	ELISHA	78	79	61	88	64	63	55	90	6
9	6	FLOWER	46	92	62	98	45	44	60	88	6
10	7	GAUTAM	90	50	60	87	50	89	48	57	6
11	8	HAPPY	55	67	17	25	25	56	60	11	4
Sheet8

Cell Formulas
Range	Formula
K4:K11	K4	=(C4>=33)+MIN((D4>=33)+(E4>=33)+(I4>=33),2)+MIN((F4>=33)+(G4>=33)+(H4>=33)+(J4>=33),3)

 

[nskatoch]

One way (I changed some values in the last row for testing):

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	6
5	2	BALLU	50	45	65	45	12	55	42	40	6
6	3	CRAYAN	67	67	63	66	45	3	41	44	6
7	4	DOLLY	33	78	64	77	67	78	80	81	6
8	5	ELISHA	78	79	61	88	64	63	55	90	6
9	6	FLOWER	46	92	62	98	45	44	60	88	6
10	7	GAUTAM	90	50	60	87	50	89	48	57	6
11	8	HAPPY	55	67	17	25	25	56	60	11	4
Sheet8

Cell Formulas
Range	Formula
K4:K11	K4	=(C4>=33)+MIN((D4>=33)+(E4>=33)+(I4>=33),2)+MIN((F4>=33)+(G4>=33)+(H4>=33)+(J4>=33),3)

One way (I changed some values in the last row for testing):

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	6
5	2	BALLU	50	45	65	45	12	55	42	40	6
6	3	CRAYAN	67	67	63	66	45	3	41	44	6
7	4	DOLLY	33	78	64	77	67	78	80	81	6
8	5	ELISHA	78	79	61	88	64	63	55	90	6
9	6	FLOWER	46	92	62	98	45	44	60	88	6
10	7	GAUTAM	90	50	60	87	50	89	48	57	6
11	8	HAPPY	55	67	17	25	25	56	60	11	4
Sheet8

Cell Formulas
Range	Formula
K4:K11	K4	=(C4>=33)+MIN((D4>=33)+(E4>=33)+(I4>=33),2)+MIN((F4>=33)+(G4>=33)+(H4>=33)+(J4>=33),3)

=SUM(C4:J4)-IFERROR(INDEX(C4:J4,MATCH(1,--(C4:J4<33),0)),MIN(F4:J4))
With the above formula I fulfil the first and second condition but not third condition. Please suggest

 

[Cubist]

=SUM(C4:J4)-IFERROR(INDEX(C4:J4,MATCH(1,--(C4:J4<33),0)),MIN(F4:J4))
With the above formula I fulfil the first and second condition but not third condition. Please suggest

I don't have a better suggestion but your formula replaces the first instance less than 33. I would think you want to replace the lowest score.
e.g. 60 23 12. It replaces the 23 when it should replace the 12.

 

[nskatoch]

I don't have a better suggestion but your formula replaces the first instance less than 33. I would think you want to replace the lowest score.
e.g. 60 23 12. It replaces the 23 when it should replace the 12.

How can I achieve the following condition:

• 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)

 

[Eric W]

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