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

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
306
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Is this what you mean?
=SUMIFS(REF!$C$3:$C$500,REF!$B$3:$B$500,T3,REF!$A$3:$A$500,$S$3)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0
It would be better if S3 had the value that you want to match in col A
 
Upvote 0
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.
 
Upvote 0
If S3 has E12 & that is what is in col A, then no change is needed.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top