turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 175K Members
- 4,549 Online
- 43.8K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Excel formula help

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Steven Elphick

New Contributor

02-06-2018
03:44 PM
- last edited on
07-25-2018
10:59 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018
03:44 PM
- last edited on
07-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

Labels:

18 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 04:03 PM - edited 02-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

Best Response confirmed by
Steven Elphick (New Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2018 04:06 PM

Thanks Damien,

Works exactly as requested. Thank you

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2018 03:47 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2018 05:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2018 05:22 PM

Glad to help!

All the best

Damien

All the best

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2018 02:25 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2018 08:27 PM

I am not sure why you cant get it?

Is this what you are trying to do?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-06-2018 08:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 10:26 AM

I'll upload the workbook :)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 10:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-07-2018 02:22 PM - edited 03-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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2018 01:49 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-08-2018 05:36 PM

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
10-03-2017
87.6K
Views

0 Likes

57 Replies

Security Warning: Automatic update of links has been disabled.

Mark Adams
in
Excel
on
03-11-2018
2,114
Views

0 Likes

6 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft