Can't get multiple criteria for SUMIFS to work like I want

[Nanaia]

My goal is to modify (or change completely) the formula located in cells V3, W3, X3, Y3 within the CHEATER tab so that they look for the data in both column T and also the number we will enter into cell S3.
What this chart does is count the number of ICP parts needed for each job. I want to make it so I can also identify the total number needed of each kind of ICP parts needed for each elevation not just the job as a whole. I thought I could add a VLOOKUP to the formula but I'm not sure how and what I've tried hasn't worked.
Since I can't easily copy and paste the information into this post, I've saved the file to a Share Drive and provided a link to it below.


https://1drv.ms/x/s!AskYMgFiAqmA0BsSdR9vLkfM18xO

 

[Nanaia]

Theoretically, yes. However for whatever reason it isn't. If I go in and remove the E in front of all the elevations in col A then they are all counted correctly. If the E isn't removed it doesn't count anything, the results are zeros. Looking more closely at the cells in col A it appears when the CSV's are created there are multiple spaces put at the end of the data. Am I correct in guessing that if there isn't a letter in the cell Excel ignores the extra spaces and if there is a letter Excel has a problem? Any ideas on how I resolve this? I would like to not have to go through up to 600 rows of data to remove the E's. I considered using the LEFT function but the elevations range from 1 digit to 5 or 6. Sometime is has letters, sometimes it doesn't.

 

[Fluff]

In an empty cell put
=CODE(RIGHT(A9))
where A9 is a cell with the extra spaces at the end, what dose the formula return?

 

[Nanaia]

A9 contains E12 with twelve spaces at the end of it.

The results of your formula is 32.

 

[Fluff]

In that case you can select the entire column > Ctrl H > in find what put a space > leave the replace with empty > Replace all

 

[Nanaia]

To clarify, I would need to use find/replace to remove all the spaces in order for that formula to work. Correct?
My coworkers will not understand this. They understanding cutting and pasting the CSV into the CSV tab but not much else when working with Excel. If I make it too complicated for them they will refuse to use the spreadsheet.
Are there any other options or work arounds?

 

[Fluff]

You could use
REF!$A$3:$A$500,$S$3&"*"

BUT if S3 is E21, it will count E21 along with E210, E211 etc
How is the data getting from the CSV sheet to the REF sheet

 

[Nanaia]

The data is getting from the CSV to the REF sheet by =CSV!A9 on the REF sheet.
I tried changing the original formula that you helped me develop and have it look directly as the CSV sheet instead of the REF sheet but got the same results so I switched it back to looking at the REF sheet to keep it consistent by having all formulas in that table pulling data from the same sheet.

 

[Fluff]

In that case change the formula to
=TRIM(CSV!A9)

 

[Nanaia]

WOOT! And you show your excellent skills once again! That made it work properly. Thank you!

 

[Fluff]

You're welcome