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

 

[Fluff]

Is this what you mean?
=SUMIFS(REF!$C$3:$C$500,REF!$B$3:$B$500,T3,REF!$A$3:$A$500,$S$3)

 

[Nanaia]

I tried that before but it gave me an error. When I try it now I am only able to get that to work in cell V3. When I try to apply the ,REF!$A$3:$A$500,$S$3 into the formulas in the next three columns it gives me a VALUE! error.

 

[Fluff]

That's because in the other columns you are looking at rows 3 to 499, rather than 3 to 500. The ranges need to be the same size so you will need to make sure they are all the same.

 

[Nanaia]

You are AWESOME! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Nanaia]

On this solution, how does it need to be modified so the ,REF!$A$3:$A$500,$S$3 will pull results even if S3 has a letter included with the number? Like E12 instead of 12.

 

[Fluff]

It would be better if S3 had the value that you want to match in col A

 

[Nanaia]

It would match the value in col A. The value fluctuates. Sometimes the data entry puts an E in front of it because it signifies elevation. We never know when the E will be added though. It's on the whim of the customer. Whatever value we put in S4 would be based off of the data in col A though.

 

[Fluff]

If S3 has E12 & that is what is in col A, then no change is needed.