• 149K Members
• 3,412 Online
• 36.6K Conversations
SOLVED

Highlighted
New Contributor

## 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

18 Replies
Solution

## Re: Excel formula help

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

## Re: Excel formula help

Thanks Damien,

Works exactly as requested. Thank you

## Re: Excel formula help

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

## Re: Excel formula help

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

## Re: Excel formula help

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

## Re: Excel formula help

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

All the best
Damien

## Re: Excel formula help

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

## Re: Excel formula help

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

## Re: Excel formula help

I am not sure why you cant get it?

Is this what you are trying to do?

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

## Re: Excel formula help

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

## Re: Excel formula help

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.

## Re: Excel formula help

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

## Re: Excel formula help

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

## Re: Excel formula help

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.

## Re: Excel formula help

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.

All the best with your Excel!

Cheers
Damien

## Re: Excel formula help

Learnt something new...nice to know how to do things simply
Related Conversations
Copy/paste no longer working in Excel
Jon Firooz in Excel on
44 Replies
EXCEL DO NOT SHOW GRAPH MAP CHART
Mark 777 in Excel on
47 Replies
Split data from one column into two columns
Jonni Anderson in Excel on
6 Replies
align decimals in a column
Jonni Anderson in Excel on
15 Replies