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

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,350
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 11, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
46,350
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 11, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You are AWESOME! Thank you! (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,350
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
46,350
Office Version
  1. 365
Platform
  1. Windows
It would be better if S3 had the value that you want to match in col A
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
46,350
Office Version
  1. 365
Platform
  1. Windows
If S3 has E12 & that is what is in col A, then no change is needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top