-
October 15th, 2017, 05:56 PM
#1
'SUMIFS' function does not distinguish between text 021 and 21
I'm using the following 'SUMIFS' where column H is formatted as text and contains 021 and 21. However the function is unable to distinguish between the two.
=SUMIFS('Trial Balances'!$G:$G,'Trial Balances'!$H:$H,'Consol by company'!E$1,'Trial Balances'!$J:$J,'Consol by company'!$A21)
I'd be grateful if someone could come up with a solution to this.
Many thanks
Wilf
-
October 16th, 2017, 11:26 AM
#2
So your matching criteria for range1 is the value at:
'Consol by company'!E$1
if you were wanting a match to equal that value, you would need to specify that. See the link below:
https://www.techonthenet.com/excel/formulas/sumifs.php
-
October 16th, 2017, 12:45 PM
#3
Thanks for getting back to me.
I've specified the value but still have the same problem in that the function doesn't distinguish between "021" and "21" even though the columns are formatted as text.
Wilf
-
October 16th, 2017, 01:50 PM
#4
After some additional research and testing, it appears that once again, Microsoft is assuming it knows what you want to do instead of providing a means for you to tell the function what your data is.
Several of the built-in Excel functions will assume that a numeric string of characters is a number even if you have it formatted as text. SUMIF and SUMIFS are two of those functions.
To get the SUMIFS function to work as you desire, you will need to either add a leading (prefix) letter to your Criteria values, or add a trailing (suffix) letter or character.
https://www.excelbanter.com/excel-wo...ding-000s.html
https://a4accounting.com.au/sumifs-function-warning/
Alternatives include using a different function, a pivot table, or a VBA macro to produce the desired results.
-
October 19th, 2017, 05:12 PM
#5
Thanks again for your help and the links. Looks as though I'll have to take your advice and prefix the numbers with an alphabet character. Annoying because at the moment the data is extracted directly from the database and I'll have to add this step.
Regards
Wilf
-
October 19th, 2017, 05:31 PM
#6
An Excel VBA macro could be used to add a prefix character if you want to use the SUMIFS function, or a macro could be used to replace the SUMIFS functionality.
Thread Information
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|