Feb 06 2018
03:44 PM
- last edited on
Jul 25 2018
10:59 AM
by
TechCommunityAP
Feb 06 2018
03:44 PM
- last edited on
Jul 25 2018
10:59 AM
by
TechCommunityAP
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
Feb 06 2018 04:03 PM - edited Feb 06 2018 04:04 PM
SolutionHi 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
Feb 06 2018 04:08 PM - edited Mar 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
Damien
Feb 26 2018 06:07 AM
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
Feb 26 2018 03:47 PM
Feb 26 2018 05:17 PM
got it works perfectly. Also tried the sumifs function to add more conditions to exclude. Thank you
Mar 05 2018 08:32 AM - edited Mar 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
Mar 06 2018 02:25 PM
Mar 06 2018 08:27 PM
I am not sure why you cant get it?
Is this what you are trying to do?
Mar 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.
Mar 07 2018 10:33 AM
Should I still make a new post? I uploaded my workbook.
Mar 07 2018 02:22 PM - edited Mar 07 2018 02:23 PM
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
Mar 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.
Mar 08 2018 01:49 PM
Mar 08 2018 05:36 PM
Feb 06 2018 04:03 PM - edited Feb 06 2018 04:04 PM
SolutionHi 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