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