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

#### Nanaia

##### Board Regular
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.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Fluff

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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

##### Board Regular

You are AWESOME! Thank you!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### Nanaia

##### Board Regular

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

##### MrExcel MVP, Moderator
It would be better if S3 had the value that you want to match in col A

#### Nanaia

##### Board Regular
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

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

Replies
1
Views
256
Replies
2
Views
212
Replies
21
Views
810
Replies
7
Views
416
Replies
1
Views
389