• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

sumifs and trim|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / sumifs and trim|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…sumifs and trim
sp_PrintTopic sp_TopicIcon
sumifs and trim
Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
1
January 20, 2018 - 7:01 am
sp_Permalink sp_Print

Hello.

I have a question about sumifs and trim. Is this possible?

I am using Excel 2016.

I have a workbook that the sumifs will look at multiple tabs.

=SUMIFS('4_Std'!J:J,'4_Std'!G:G,A:A,'4_Std'!H:H,B:B,'4_Std'!X:X,$H$4)

My Invoice tab will look at my press tab to pull the quantity over, it must match the store name and store #, then match that to its run type that must match H4.

My questions are this, can I add trim to this function.  When typing in the Store #, Store Name and/or Run type, sometimes a space gets added to the end.  We all know that if that happens the formula won’t pull in the correct quantity.

I appreciate all your help.

Thanks so much

Amy

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
January 20, 2018 - 7:49 am
sp_Permalink sp_Print

Hello Amy,

Yes, it should work just fine. Just give it a shot.

=TRIM(SUMIFS('4_Std'!J:J,'4_Std'!G:G,A:A,'4_Std'!H:H,B:B,'4_Std'!X:X,$H$4))

Br,
Anders

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
3
January 20, 2018 - 9:17 am
sp_Permalink sp_Print sp_EditHistory

Anders

Thank you so much.  This didn't seem to work for me.  It kept the qty, but when I went in to test by adding a space, it zero'd out the qty.

One more question.  When I add a sum at the bottom, it doesn’t add these together.  I change the format to numbers, but it’s still not adding them together It was working until I added in the trim function. Any suggestions?

Thanks again for your help.

Amy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
January 20, 2018 - 10:27 am
sp_Permalink sp_Print

Hi Amy

I don't think you can add a TRIM like that to remove excess spaces from your data range. To handle cases where there could be leading and/or trailing spaces, you can add an asterisk to the criteria. Example : SUMIFS(B2:B7,A2:A7,"*"&D2&"*")

 

There is an error in your formula =SUMIFS('4_Std'!J:J,'4_Std'!G:G,A:A,'4_Std'!H:H,B:B,'4_Std'!X:X,$H$4)

Your criteria (in red) should only refer to one cell, not a range of cells.

This could be the cause of your SUMIFS problem in your 2nd question. Can't be sure unless we can see your sample data.

Hope this helps.

Sunny

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
January 21, 2018 - 8:11 am
sp_Permalink sp_Print

Hi Amy,

I need to revise my previous reply. As Sunny writes, it does not work to add TRIM() as I mentioned. I am sorry for that.
I set up a test file myself to check and this is what I found.

=TRIM(A1) works fine.
=TRIM(A:A) do not work at all.
=TRIM(Sheet2!F2) works fine.
=TRIM(Sheet2!F1:F3) do no work at all.

The syntax for SUMIFS() is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

So this means that when using SUMIFS() we can only use TRIM() when referencing to one cell and that is the criteria, meaning that any extra spaces in the criteria range will remain as TRIM() don't work with ranges. So for your case scenario Amy there seem to be no other solution than to either manually trim the data or use a macro to automate such action.

What works is below formula, which I write in Sheet1, where I also have my criteria 1 and criteria 2 in cells A1 and A2.
=SUMIFS(Sheet2!A:A,Sheet2!B:B,TRIM(A1),Sheet2!C:C,TRIM(A2))

And lastly, the TRIM() function only works with text, obviously.

To be better off to give you any good help Amy it would be great if you can provide a sample of your data.

@Sunny, thank you for correcting me. Taking the easy way and assume things also means to take a seat on that stool above the water tank and wait for that ball hitting the trigger... 😉

Br,
Anders

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
January 21, 2018 - 3:33 pm
sp_Permalink sp_Print

@anders - offering solutions without seeing the data is like shooting in the dark. Lots of misses and some rare lucky hits. Been there, done it.

@amy - since you can't trim your data, just give my solution a try. It will not work if there are extra spaces between words. It will only handle leading and trailing spaces. Like anders said, it is best if you can clean (trim) your data. There are lots of free utilities/VBA codes that can do that. Do attach some sample data if our solution does not work for you. It will allow us to see whether the data is the actual cause of your problem.

Sunny

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
7
January 23, 2018 - 12:54 am
sp_Permalink sp_Print sp_EditHistory

Sunny & Anders

Thanks for all your help. I have attached a sample. I agree it’s better to clean up the date beforehand.

 I haven’t had any issues with the cell range, but maybe I’m not seeing something I should.

“There is an error in your formula =SUMIFS('4_Std'!J:J,'4_Std'!G:G,A:A,'4_Std'!H:H,B:B,'4_Std'!X:X,$H$4)”

 

Can you also help me with another formula?

On the Invoice Master tab, column G, I need to pull in the price from the table E16:J72, matching the Web, column F, but also match up the paper type, column E to E47:J47.

The VLOOKUP won’t work because the column number can change depending on the paper type.

Thanks again for all you assistance.  This forum is AWESOME.

Sincerely

Amy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
January 23, 2018 - 10:31 am
sp_Permalink sp_Print

Hi Amy

Thanks for your sample. We now have a better picture of your request.

Please refer attachment. Refer the columns in yellow.

Hope this helps.

Sunny

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
9
January 24, 2018 - 12:41 am
sp_Permalink sp_Print

Sunny

This worked amazingly.  Thank you so much for all your help.  I appreciate you taking the time to make my job easier.

Thanks again,

Amy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
January 24, 2018 - 2:13 am
sp_Permalink sp_Print

Hi Amy

Happy to know it is working for you.

Cheers.

Sunny

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
11
January 27, 2018 - 8:37 am
sp_Permalink sp_Print

Sunny

I found one issue with these formulas, I am hoping can be easily solved.

We switch between tabs when the formats change. We cut and insert the data from one tab to another. This keeps formulas, etc from breaking.

This formula doesn’t move with the different tabs.  Is there a way to write this that when we change tabs, it will change as well?

 

=SUMIFS('4_Std'!J:J,'4_Std'!G:G,$A7,'4_Std'!H:H,$B7,'4_Std'!X:X,H$5)

So, when I go to the 12 tab, the above formula needs to change as too.

Thanks so much.

Amy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
January 27, 2018 - 10:54 am
sp_Permalink sp_Print

Hi Amy

I am not clear about what you wanted.

Can you give an example of what you are doing and what is the expected result?

Sunny

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
13
January 30, 2018 - 12:25 am
sp_Permalink sp_Print

Sunny

Good morning.

There are three tabs at the bottom of this example. An example would be the first week, we run the 4 Std, all the information would be on this worksheet (tab), rows 9 through 23. On the invoice master tab, columns H, I, J, the formula looks at this 4_Std worksheet.

The next week, we cut and insert the information from the 4_Std worksheet and move it to the 12_TAB worksheet.  But when I look at the _Invoice Master worksheet, the formulas in H, I, J are still linked up the to he 4_Std worksheet.

Or a third example is we must split the information, half on the 4_Std worksheet, the other half on the 12 Tab worksheet.

Is there a way to write the formula where it will move if we move the information from one worksheet to another?

I know if we do a simple “='4_Std'!J23” (example in on the _Invoice Master, k7) in columns H, I, J, the information will update if we move the rows from worksheet to worksheet.  But if information gets moved, these break very easily.

I appreciate your time.

Amy

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
14
January 30, 2018 - 11:38 am
sp_Permalink sp_Print

Hi Amy

There are still a lot of questions that need to be asked.

Is 12_Tab an accumulated file? (i.e. every week new data will be appended to this sheet from 4_Std) You did not specify if this is so.

If it is not so , then why cut and paste? You can just easily rename the 4_Std tab to 12_Tab and the formula in the Invoice Master will refer to this file (you will first need to rename your original 12_Tab to another name)

Are you expecting the formula in Invoice Master to refer to 12_Tab instead of 4_Std after you cut and paste to the 12_Tab?

What formula are you expecting in columns H to J of the Invoice Master after the cut and paste?

You can give this a try to see if it works for you. In the Invoice Master, columns H to J refers to entire columns such as SUMIFS('4_Std'!J:J,'4_Std'!G:G,$A7,'4_Std'!H:H,$B7,'4_Std'!X:X,H$5)

Try changing it to SUMIFS('4_Std'!J1:J26,'4_Std'!G1:G26,$A7,'4_Std'!H1:H26,$B7,'4_Std'!X1:X26,H$5), for example. Now cut out the required range (including headers) and paste.

You must describe your entire process flow to us. Otherwise we may not be able to suggest to you a working solution.

Sunny

Avatar
Amy 73
Member
Members
Level 0
Forum Posts: 39
Member Since:
February 11, 2015
sp_UserOfflineSmall Offline
15
January 31, 2018 - 1:30 am
sp_Permalink sp_Print

Sunny

Each tab in already set up with unique information on it.  We only cut the information below the header line in the 4_Std, row 9 and below, this gets cut from one worksheet to another.  

“Are you expecting the formula in Invoice Master to refer to 12_Tab instead of 4_Std after you cut and paste to the 12_Tab?” – Yes, I want the formula to move when I cut and paste the information from the 4_Std to the 12_Tab.

But changing the formula to the required range, instead of the whole column, this worked.

Thanks again for all your help. 

Amy

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, David Eva
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27238

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.