SUM/SUMIF - cutoff value

[MGhell]

Hi,

is it possible to create a formula which creates a sum of a range(A1:E1) but values > 8 are cutoff?

Example:

A1= 8,5
B1 = 10
C1 = 8
D1 = 7
E1 = 0

SUM should be 8+8+8+7+0

Cheers

Max

 

[Scott T]

Try

Excel 2010
	A	B	C	D	E
1	8.5	10	8	7	0
2
3	31
Sheet5

Cell Formulas
Range	Formula
A3	{=SUM(IF(A1:E1>8,8,A1:E1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER
=SUM(IF(A1:E1>8,8,A1:E1))

 

[snjpverma]

Try this and confirm with just Enter

=SUMIF(A1:E1,"<9",A1:E1)

 

[MGhell]

Thanks....works like a charm

 

[Jonmo1]

You're welcome.

For a non array version, maybe
=SUMIF(A1:E1,"<=8")+COUNTIF(A1:E1,">8)*8