sum if

[Richard]

I want to add the values from cell A1 to A6. However, if any values is
greater 8, I must minus .5, then add them. If they not greater 8, then just
add them to the total. How do I do this in an sum if statement?

 

[Tom Ogilvy]

=SUM(IF(A1:A6>8,A1:A6-0.5,A1:A6))

entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

If you reselect the cell and look in the formula bar, it will appear as
{=SUM(IF(A1:A6>8,A1:A6-0.5,A1:A6))}

Excel displays it enclosed in curly brackets if it is being treated as an
array formula.
if it is not displayed that way, hit F2, then again, close it while
simultaneously doing Ctrl+Shift+Enter

 

[Rick Rothstein \(MVP - VB\)]

This non-array (meaning just use Enter to commit it Richard) should also
work...

=SUM(A1:A6)-0.5*COUNTIF(A1:A6,">8")

Rick

 

[www.exciter.gr: Custom Excel Applications!]

i dont know if this suits you, it is not sumif but it works

=SUM(A1:A6)-COUNTIF(A1:A6;">8")*0,5

First part is total sum and the second counts the values >8 and
multiplies their number * 0.5. Then is part1-part2

Best