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 fr...
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

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
    Mar 06, 2018

    I am not sure why you cant get it?

    Is this what you are trying to do?

     

    SUMIF(C5:C9,"CIP",D5:D9)

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

       

      it is basically what I am trying to do.  I have uploaded the workbook.  I need to figure out how to get the total CIP per employee per week. 

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

      I'll upload the workbook :)

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Mar 06, 2018
    Hi Tammy

    Could I suggest that you make a new post on this so we can look at it?

    If possible, could you also upload a sample of the file for review?

    Cheers
    Damien
    • Tammy Duggan's avatar
      Tammy Duggan
      Copper Contributor
      Mar 07, 2018

      Should I still make a new post?  I uploaded my workbook.

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Mar 07, 2018

        Hi Tammy

         

        I've amended your worksheet with a formula that will do the trick.

         

        Essentially the formula is just adding each worksheet and totals the value in your summary sheet (it's a simple formula given you only have 7 worksheets to calculate).

         

        =SUMIF('M-4'!C3, "CIP", 'M-4'!F3)+SUMIF('T-4'!C3, "CIP", 'T-4'!F3)+SUMIF('W-4'!C3, "CIP", 'W-4'!F3)+SUMIF('TH-4'!C3, "CIP", 'TH-4'!F3)+SUMIF('F-4'!C3, "CIP", 'F-4'!F3)+SUMIF('S-4'!C3, "CIP", 'S-4'!F3)+SUMIF('SU-4'!C3, "CIP", 'SU-4'!F3)

         

        Let us know how you go?

         

        Cheers

        Damien