Log In
Register
Home
February 11, 2015
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
February 11, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
@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
February 11, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
February 11, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
Hi Amy
Happy to know it is working for you.
Cheers.
Sunny
February 11, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
February 11, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
February 11, 2015
Offline
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
1 Guest(s)
