I use CONCATENATE to create unique strings of text in my data pulls such as Department Code (3001), Employee ID (123456789), Date in numerical format (ie 11/17/2023 = 45247, 11/24/2023 = 45254, and so on).
The CONCAT string Code + Employee ID + Date becomes 300112345678945247, and the last 5 digits would change as the date changes.
I use this to then perform a SUMIF on another tab, which uses the exact same CONCAT string (ie 300112345678945247). Instead of returning hours for a certain week with that unique code, Excel is returning ALL hours for all weeks, ignoring the Date portion of the CONCAT string.
Here's the weird part. If I were to change the Department Code (3001) to a word or even a letter, let's say, Company1 or C, in both sides of the lookup (data tab and working tab) everything works as intended. Even more strange, there are two data tabs (one my company and one Subcontractors). This issue is only present on the Subcontractors data, even though both are formatted the exact same way, for some reason Excel does not like it when I switch from the Word to the Number. I don't know if that necessarily means that's the issue - it could be something entirely different throwing it off, my point is that's the only thing I've changed, and that seems to be the difference between the formula working and not working.
My workaround is using SUMIFS for the different criteria, and that pulls in the data correctly, but I'd still like to understand why the CONCAT formula is not working properly.
Thank you.
