- last edited on
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
MTD xxx xxx xxx xxx $50
MTD xxx xxx xxx xxx $20
MTD xxx xxx xxx xxx $10
XXX xxx xxx xxx xxx $05
Thanks in advance
02-06-2018 04:03 PM - edited 02-06-2018 04:04 PMSolution
If I am understanding you right, try this formula:
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?
02-06-2018 04:08 PM - edited 03-06-2018 08:30 PM
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
02-26-2018 06:07 AM
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
02-26-2018 03:47 PM
03-05-2018 08:32 AM - edited 03-07-2018 10:29 AM
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
03-06-2018 02:25 PM
03-07-2018 10:30 AM
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.
03-07-2018 02:22 PM - edited 03-07-2018 02:23 PM
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?
03-08-2018 06:31 AM
Thank you Damien. That is pretty simple. I had seen some formulas with "indirect" and "sumproduct" and I am completely confused. Simple is much better. Thank you for your help. I wish I could just sit with you all day and you share the knowledge.
03-08-2018 01:49 PM