Excel Stopping values converting to dates and subsequently returning correct SUMIFS results
Hi Experts
Hope you can help
I have a sheet that is going to be the basis of outlining the performance of our warehouse stock locations in terms of anomalies
Our stock locations are made up of three double digits separated by a hyphen (eg. 01-01-A1)
To obtain these figures I’ve converted the locations by trimming them into the first set of two digits (eg. 01-01) and using SUMIFS to total and display the results.
The main report data is in Columns A to I, with column I containing the stock figures
The trimmed filter column containing the trimmed locations (eg. 01-01) is in Column J
The results data are in Columns O and P and the results data is obtained by using =SUMIFS(J:J,I:I,O:O)
Ive attached the file in question
Unfortunately I have encountered two issues:-
1) Column O, even when converted to text, is producing some of the trimmed locations as Date format and also;
2) There are some locations in the results column (P) that are not present in the location data but still they are producing results from the SUMIFS even though they are not present in the main data in Column A to I (example, 07-19 has 1 as a result in Column P but this is not present in Column J)
Is there a way of eradicating the conversion to dates in Column O and to produce the correct SUMIFS results based on the available data in Columns A to I and subsequently Column J?
Any help would be much appreciated
Thanks
J
Copy-of-Pick-Face-Map.xlsx
Hope you can help
I have a sheet that is going to be the basis of outlining the performance of our warehouse stock locations in terms of anomalies
Our stock locations are made up of three double digits separated by a hyphen (eg. 01-01-A1)
To obtain these figures I’ve converted the locations by trimming them into the first set of two digits (eg. 01-01) and using SUMIFS to total and display the results.
The main report data is in Columns A to I, with column I containing the stock figures
The trimmed filter column containing the trimmed locations (eg. 01-01) is in Column J
The results data are in Columns O and P and the results data is obtained by using =SUMIFS(J:J,I:I,O:O)
Ive attached the file in question
Unfortunately I have encountered two issues:-
1) Column O, even when converted to text, is producing some of the trimmed locations as Date format and also;
2) There are some locations in the results column (P) that are not present in the location data but still they are producing results from the SUMIFS even though they are not present in the main data in Column A to I (example, 07-19 has 1 as a result in Column P but this is not present in Column J)
Is there a way of eradicating the conversion to dates in Column O and to produce the correct SUMIFS results based on the available data in Columns A to I and subsequently Column J?
Any help would be much appreciated
Thanks
J
Copy-of-Pick-Face-Map.xlsx
ASKER
Hi Roy
Thank you for the advice
Ive tried the custom formatting as you suggested however the entries are still showing as dates instead of converting to the custom value
Also, I have tried converting to text by highlighting the entries however it is still asking to change or ignore the error and I am still getting the SUMIFS error of having results appear from non-entries in Column J
Regards
J
Thank you for the advice
Ive tried the custom formatting as you suggested however the entries are still showing as dates instead of converting to the custom value
Also, I have tried converting to text by highlighting the entries however it is still asking to change or ignore the error and I am still getting the SUMIFS error of having results appear from non-entries in Column J
Regards
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you sorted it
Alternatively, format as Text