Pivot Table Sum giving Zeros but count works

[aspengarza]

I have a workbook that is helping my team track weekly inventory of something in our lab. We scan one barcode that contains the information required for weekly inventory (Lot ID, Variety, and number of pouches). The next three columns after the scan will split the text by a delimiter using the TEXTSPLIT() function. Originally, when we were scanning 3 separate barcodes for the information, I could use the SUMIFS() function to calculate the totals based of 2 criteria. The SUMIFS function cannot read a cell if it is populated by a formula, so I switched to using pivot tables.

I created 2 pivot tables: One with the expected values for inventory and one with the "Scan to Count" values.
I need the Scan to Count pivot table to sum all the pouches for one variety per day it is scanned. Right now the "Count" version will work on the pivot table; If I scan in 8 boxes each with 50 pouches, it will display 8. But when I switch to "Sum" it changes all values in the pivot table to 0.
I found other posts saying to ensure the data source is in number format, so I did this on the entire column of "Number of Pouches" but this did not fix the issue.

I am now wondering if it is the same issue as the SUMIFS() function, where the pivot table can't read the result of a formula. I attached an image, but can't download the add on to allow for a mini-sheet.
Any ideas?
Thanks!
Aspen Garza

 

[RoryA]

The results of TEXTSPLIT are text, nut numbers, which is why they sum as 0 for both the pivot table and the SUMIFS formula. What is the actual textsplit formula?

 

[aspengarza]

The exact formula is: =TEXTSPLIT(Table7[@[Data Matrix]],"-")
We are scanning information into Table 7, the column is titled Data Matrix, and the delimiter is a hyphen "-"
Is there another way to split text by a delimiter so that it does not cause these issues?

 

[RoryA]

If it's ok to convert anything that looks like a number into a number you could use something like:

=LET(d,TEXTSPLIT(Table7[@[Data Matrix]],"-"),IFERROR(--d,d))

 

[aspengarza]

This worked perfectly for what I needed! Thank you so much for your quick responses.
What is the 'd' in that formula though?

 

[RoryA]

d is just a variable that holds the result of the TEXTSPLIT formula. Without it, you would have to repeat that part like this:

=IFERROR(--TEXTSPLIT(Table7[@[Data Matrix]],"-"),TEXTSPLIT(Table7[@[Data Matrix]],"-"))