Excel formula help

[Steven Elphick]

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

[#2 · Accepted solution]

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

[#3]

Thanks Damien,

Works exactly as requested. Thank you

[#4]

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

[#5]

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

[#6]

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

[#7]

got it works perfectly. Also tried the sumifs function to add more conditions to exclude. Thank you

[#8]

Glad to help!

All the best
Damien

[#9]

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

[#10]

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

[#11]

I am not sure why you cant get it?

Is this what you are trying to do?

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

[#12]

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

[#13]

I'll upload the workbook :)

[#14]

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. 

[#15]

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

[#16]

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

[#17]

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.

[#18]

Hi Tammy

Happy to help!

I'm a fairly simplistic user myself and look for the easiest answer that does the job. I also looked up sumproduct and indirect and got confused so just stuck to this.

Glad it's worked out!

All the best with your Excel!

Cheers
Damien

[#19]

Learnt something new...nice to know how to do things simply