Sum of six columns with certain conditions using if statement

[nskatoch]

	C6​	D6​	E6​	F6​	G6​	H6​	I6​	TOTAL(Sum of Six Columns.
Max. marks	80​	80​	80​	80​	80​	80​	80​	480​
SAN	40	70	60	50	25	40	45
JOHN	30	35	40	26	40	45	22

In the above table, I want Sum of six columns out of seven with the following conditions,
IF (F6<27, SUM(C6,D6,E6,G6,H6,I6),
IF (G6<27, SUM (C6,D6,E6,F6,H6,I6)
IF (H6<27, SUM(C6,D6,E6,F6,G6,I6)
IF (I6<F6, SUM(C6,D6,E6,F6,G6,H6)
IF (I6<G6, SUM(C6,D6,E6,F6,G6,H6)
IF (I6<H6, SUM(C6,D6,E6,F6,G6,H6),
IF (I6<0, SUM(C6,D6,E6,F6,G6,H6).

I have applied the following formula, but it does not fulfil all the above conditions:

=IF(F6<27,SUM(C6,D6,E6,G6,H6,I6),IF(G6<27,SUM(C6,D6,E6,F6,H6,I6),IF(H6<27,SUM(C6,D6,E6,F6,G6,I6),IF(I6<0,SUM(C6,D6,E6,F6,G6,H6),IF(I6<F6,SUM(C6,D6,E6,F6,G6,H6),IF(I6<G6,SUM(C6,D6,E6,F6,G6,H6),IF(I6<H6,SUM(C6,D6,E6,F6,G6,H6),"")))))))

 

[jasonb75]

What if more than one condition is met? As with the bottom row in your example?

 

[nskatoch]

What if more than one condition is met? As with the bottom row in your example?

Sir, I am using MS office 2019.

 

[jasonb75]

That doesn't answer my question.

As far as I can see your formula will fulfill the conditions that you have specified. If it doesn't then a clearer and more detailed explanation will be needed, not just a screen capture (which is useless to us) with no explanation.

 

[nskatoch]

That doesn't answer my question.

As far as I can see your formula will fulfill the conditions that you have specified. If it doesn't then a clearer and more detailed explanation will be needed, not just a screen capture (which is useless to us) with no explanation.

Dear member in the above table, as see the value in col. TOTAL, my formula not fulfil the following conditions:
IF (I6<F6, SUM(C6,D6,E6,F6,G6,H6), the TOTAL should be 249 instead of 247
IF (I6<G6, SUM(C6,D6,E6,F6,G6,H6),the TOTAL should be 265 instead of 264
IF (I6<H6, SUM(C6,D6,E6,F6,G6,H6),the TOTAL should be 316 instead of 311

IF (I6<0, SUM(C6,D6,E6,F6,G6,H6), the TOTAL should be 245 instead of 225

 

[Fluff]

Why is Student B 308 rather than 273 as I is less than H?
The same for C as I is less than both F & G, so shouldn't it be 318?

 

[jasonb75]

You still haven't answered my question!

What if more than one condition is met?

In the case of this example from your most recent post

IF (I6<F6, SUM(C6,D6,E6,F6,G6,H6), the TOTAL should be 249 instead of 247

F6 is less than 27 so the formula is giving the first condition priority.

By striking out both F and I in your screen capture you are suggesting that both values should be ignored, in which case the correct result would be 224, not 249 as you have told us.

 

[nskatoch]

You still haven't answered my question!

In the case of this example from your most recent post

F6 is less than 27 so the formula is giving the first condition priority.

By striking out both F and I in your screen capture you are suggesting that both values should be ignored, in which case the correct result would be 224, not 249 as you have told us.

Dear Members,
In the case of student "E", the value of columns G and I are less than 27, in my formula only first condition is fulfilled i.e. IF(G6<27,SUM(C6,D6,E6,F6,H6,I6), but in the same time the of column "I" is less than then the value of column "G" i.e. IF (I6<G6, SUM(C6,D6,E6,F6,G6,H6) so the second condition not fulfilled.
=IF(F6<27,SUM(C6,D6,E6,G6,H6,I6),IF(G6<27,SUM(C6,D6,E6,F6,H6,I6),IF(H6<27,SUM(C6,D6,E6,F6,G6,I6),IF(I6<0,SUM(C6,D6,E6,F6,G6,H6),IF(I6<F6,SUM(C6,D6,E6,F6,G6,H6),IF(I6<G6,SUM(C6,D6,E6,F6,G6,H6),IF(I6<H6,SUM(C6,D6,E6,F6,G6,H6),"")))))))
And same in case of student named "F" and "G".
I want both conditions should be fulfilled at the same time.

 

[Phuoc]

Maybe like this

=SUMIF(C6:I6,">=27")

 

[Phuoc]

Or try?

=SUM(C6:I6)-MIN(C6:I6)