Tags: cell, excel, howev, microsoft, minus, msdn, software, values

I want to add the values from cell A1 to A6. Howev...

On Microsoft » Microsoft Excel

2,670 words with 3 Comments; publish: Fri, 23 May 2008 00:38:00 GMT; (30645.90, « »)

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?

All Comments

Leave a comment...

  • 3 Comments
    • =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

      --

      Regards,

      Tom Ogilvy

      "Richard" wrote:

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

      >

      >

      #1; Fri, 23 May 2008 00:39:00 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

      "Tom Ogilvy" <TomOgilvy.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:BAED1C80-02E5-4318-9403-DC4473E70FCA.excel.todaysummary.com.microsoft.com...

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

      > --

      > Regards,

      > Tom Ogilvy

      > "Richard" wrote:

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

      >>

      #2; Fri, 23 May 2008 00:40:00 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

      On Oct 27, 4:23 pm, "Richard" <nomailplease.com> wrote:

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

      #3; Fri, 23 May 2008 00:41:00 GMT