Forum Discussion

Bradley Peters's avatar
Bradley Peters
Copper Contributor
Sep 21, 2018
Solved

Using SUMIF to exclude values

Hi Guys,

 

I would like to use SUMIF, if possible, to add up a column but to exclude values when a corresponding cell contains the text "In Flight Relief". Can anybody help?

excel
Formulas and Functions
  • Lorenzo Kim's avatar
    Lorenzo Kim
    Sep 22, 2018
    can also reference a cell (Z2):
    =SUMIF(Z2:Z10,"<>"&Z2,M2:M10)
    where Z2 is the controlling text.
    be sure that the text is consistent in all controlling cells - otherwise it won't function properly.
    "In Flight Relief" is not sames as "In Flight Relief" nor "Inflightrelief"..
    If Z2 is deleted then the sum will be zero.... because the controlling cell is now blank.
    HTH

10 Replies

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Sep 22, 2018
    if you are adding a column use =SUM(range)
    this will sum only the numbers excluding strings or text cell..
    HTH
    • Bradley Peters's avatar
      Bradley Peters
      Copper Contributor
      Sep 22, 2018

      Thanks for your response Lorenzo.

       

      I think my query may have been unclear. If the column I want to sum is M I would like the function to SUM, but exclude values within column M when there is specific text in a corresponding cell (let’s say column Z).

       

      I know how use SUMIF to exclude the values when the corresponding cell has any unspecified text  but I would like to exclude those cell with specific text. 

       

      Thanks again. 

Resources