Forum Discussion

Steven Elphick's avatar
Steven Elphick
Copper Contributor
Feb 06, 2018
Solved

Excel formula help

Hi there,

 

I am looking to find the easiest formula solution for the following.

 

I wish to check if table a5:a32 contains the word "MTD", if so, it takes data from a cell in the same row from column F and then sums it in a seperate cell.

so if i had 5 MTD, 5 values would be summed together and not include the rest of data in column F
eg.
MTD xxx xxx xxx xxx $50
MTD xxx xxx xxx xxx $20

MTD xxx xxx xxx xxx $10
XXX  xxx xxx xxx xxx $05
                        total $80

 

Thanks in advance

Formulas & Functions
Requests
  • Damien_Rosario's avatar
    Damien_Rosario
    Feb 06, 2018

    Hi Steven

     

    If I am understanding you right, try this formula:

     

    =SUMIF(A2:A5,"MTD",B2:B5)

     

    Change A to your desired column where the MTD text is located.

    Change B to where the $ column is located.

     

    Basically, this formula will do what you have asked.

     

    I have also attached a sample file to demo the above for you to see it in action.

     

    Let us know how you go?

     

    Cheers

    Damien

18 Replies

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Feb 06, 2018

    Hi Steven

     

    If I am understanding you right, try this formula:

     

    =SUMIF(A2:A5,"MTD",B2:B5)

     

    Change A to your desired column where the MTD text is located.

    Change B to where the $ column is located.

     

    Basically, this formula will do what you have asked.

     

    I have also attached a sample file to demo the above for you to see it in action.

     

    Let us know how you go?

     

    Cheers

    Damien

    • Tammy Duggan's avatar
      Tammy Duggan
      Copper Contributor
      Mar 05, 2018

      I am trying to do the same thing across a workbook for each month of the year. My formula is =SUMIF('M-4:SU-4'!C15,"CIP",'M-4:SU-4'!F15)

      Each row is for an employee (15 is just one) M=Monday and SU=Sunday. CIP is job description (Construction in Progress), Column "F" is total hours for that day.
      I am trying to pull the total hours of CIP for each week for each employee and when I put the above formula in I get a #Value! error. I double checked all cells for errors and everything looks fine. I don't know what I am missing. Thank you. Tammy

      • H S Desai's avatar
        H S Desai
        Copper Contributor
        Mar 06, 2018

        Maybe because you dont have the range of cells to add?

    • H S Desai's avatar
      H S Desai
      Copper Contributor
      Feb 26, 2018

      Hi Damien

      I was looking for an answer which is exactly opp of this solution.Meaning I want to exclude any rows that contain the word "MTD" and not include in the sum total. How do I write that formula?

      sumif(A1:A5, NOT "MTD", B1:B5)

      Logic is as above but am not sure what syntax to use

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Feb 26, 2018
        Hi H S Desai

        Try:

        =SUMIF(A2:A5, "<>MTD",B2:B5)

        <> means does not equal. Make sure it's in the " " as well.

        Let me know how you go?

        Cheers
        Damien
      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Feb 06, 2018

        Glad I could help, Steven.

        Could you flag the answer as the best response so that other forum users will know what the solution is for their reference?

        All the best
        Damien