How to edit then add together values in a CSV file

Summary: This is an exported file from a paint colorant machine. I am interested in finding out how much colorant I use during a certain time period. This CSV file lists the ingredients used in columns named "Ingredient1, Ingredient2, etc.."

Problem: I'm not sure how to add up these items, as there may be hundreds and the Ingredient name (Ax, B, D, I, etc) is not always in the same column. Also, I need to edit the amounts of the columns before they are added.

For instance, the values are given in points, or fractions of an ounce. 1 point is 1/48th of an ounce. So 36 points is 36/48ths of an ounce. Ounces are listed as Y. So Y1 would be one ounce and one point, or 49 points. I believe it is easier to convert all the values with a Y to a point amount. For instance. 6Y would be 288 points. I could then add up all of the points of a certain ingredient and do a simple calculation.

I have no idea where to start. Any ideas?

Attached are what I would like calculated as a results.

Here is how they are figured out.

AX: Y = 48 shots, plus the 3 extra shots = 51
B: (96 + 5) * 2 = 202
C: (96 + 33) * 2 = 258
D: 11.25 shots
I: (96 + 39) * 2 = 270
KX: (96) * 2 = 192
V: 36 Shots

There can be more than 6, in fact there are 13 total that will be in the full spreadsheet

Originally Posted by tanasedn

Please excuse my ignorance but, 2Y5.00 means 2*48+5?
And question 2: the pairs always start from column Q?

Yes "Y" simply means 48 points, the rest of the data after the Y is also points.

The First Ingredient is always in column Q, usually we only have a max of 5 ingredients used, rarely 6. However the file always lists 6 ingredient columns.

You are a genius! One question though, if the Ingredient column only had Y in it, it came over to the Val column as Y instead of 48. How would I fix that?

That still brought over the Y into the Val column.

In Sheets1 when the amount is 0y16 it is calculating the value as 64 which would be 1y16. The 0Y16 should display only 16.

Also, are we able to add all of the ingredients up so it only has one total value for AX,

Originally Posted by tanasedn

I hope I have evaluated all the conversion possibilities.
I also inserted a Pivot Table to aggregate quantities.

You are awesome! Thank you so much!

I get an error when I try it with my full spreadsheet. I attached the full spreadsheet.