SOLVED
Home

Excel formula help

Highlighted
Steven Elphick
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

 

Thanks in advance

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

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

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

 

Re: Excel formula help

Glad to help!

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?

 

2018-03-07 (1).pngSUMIF(C5:C9,"CIP",D5:D9)

Re: Excel formula help

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

Re: Excel formula help

I'll upload the workbook :)

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.

Glad it's worked out!

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
blink a cell
Lorenzo Kim in Excel on
12 Replies
Missing Excel 2013 worksheet tabs
Mike Hill in Excel on
7 Replies