sum if

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 27 Oct 2007 15:23 GMT

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?

Reply to this Message

Tom Ogilvy - 27 Oct 2007 15:33 GMT

=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

Signature

Regards,
Tom Ogilvy

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

Reply to this Message

Rick Rothstein (MVP - VB) - 27 Oct 2007 17:30 GMT

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

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

Rick

> =SUM(IF(A1:A6>8,A1:A6-0.5,A1:A6))
>
[quoted text clipped - 14 lines]
>> just
>> add them to the total. How do I do this in an sum if statement?

Reply to this Message

www.exciter.gr: Custom Excel Applications! - 28 Oct 2007 21:29 GMT

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

Reply to this Message

Rate this thread: