Excel - How to Use a SUMIF Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

So im trying to use a sumif formula, but i need it to not look for a range for one specific criteria but instead look for what one specific field says. Example Below =sum(sumifs(QuantityMC,TeamsMC,A6,TypeMC,B2)) The above formula works as on the specific MC sheet an entire column is dedicated for "TypeMC". I want to be able to delete this column and have "TypeMC" to be a single field as it will be the same for every item on that sheet but may differ on a different sheet.
Solved by M. H. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/10/2018 - 02:42
Hello, I understand that you to be able to use a SUMIFS and have it dynamically look for something instead of having to always look for TypeMC, right?
User 10/10/2018 - 02:43
so TypeMC is a named range for one specific sheet
User 10/10/2018 - 02:43
but i dont need it to be a whole column, it can just be asingle field
User 10/10/2018 - 02:43
say x
User 10/10/2018 - 02:44
but on the next sheet it may be x or y or z
User 10/10/2018 - 02:44
this is essentially to read all the sheets and return anything marked x
Excelchat Expert 10/10/2018 - 02:44
It's a bit difficult to visualize. Are you able to share your file to help me understand your requirement better?
User 10/10/2018 - 02:44
yeah 2 seconds
Excelchat Expert 10/10/2018 - 02:44
Okay, that would've been a better way to describe your requirement. To look at different sheets.
User 10/10/2018 - 02:46
i cant add my workbook here so i think ihave to go back through from the start
Excelchat Expert 10/10/2018 - 02:47
If your file is a macro enabled file, save it AS .xlsx and then use the clip icon next to this chat to upload.
User 10/10/2018 - 02:47
there is no clip icon though
User 10/10/2018 - 02:47
wait there it is
User 10/10/2018 - 02:48
it hadnt loaded for whatever reason
User 10/10/2018 - 02:48
[Uploaded an Excel file]
Excelchat Expert 10/10/2018 - 02:48
Thank you. May I know which sheet and which column should I be looking into?
User 10/10/2018 - 02:48
so this is team totals
User 10/10/2018 - 02:49
the citroen value
Excelchat Expert 10/10/2018 - 02:49
Okay, I'm in there. PLease explain your requirement again and this time reference the ranges you are referring to.
User 10/10/2018 - 02:50
so i want it to look at the Monte Carlo sheet
User 10/10/2018 - 02:51
add the quantitys of all the team citroen orders and return them, but only if it is marked A3 Mapbooks to the right of the type 2 field
Excelchat Expert 10/10/2018 - 02:52
I see. I think I understand what you are trying to do.
User 10/10/2018 - 02:52
the map type column i plan on deleting, as everything on the sheet will be 1 type
User 10/10/2018 - 02:52
yeah sorry i think im making it more confusing
Excelchat Expert 10/10/2018 - 02:53
I may be able to help but I'll need more information and some clarifications.
Excelchat Expert 10/10/2018 - 02:53
By the way, Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User 10/10/2018 - 02:53
Excelchat Expert 10/10/2018 - 02:54
So if you are going to remove the Map type then there's no need for the Map type criteria then as they will all be the same?
User 10/10/2018 - 02:54
for this sheet yes
User 10/10/2018 - 02:54
but on other sheets it may be different
Excelchat Expert 10/10/2018 - 02:54
That part doesn't make any sense.
Excelchat Expert 10/10/2018 - 02:54
Then why not simply remove the map type criteria?
User 10/10/2018 - 02:55
ok sorry im making it more complicated than it needs to be
User 10/10/2018 - 02:55
there will be more sheets in the future
Excelchat Expert 10/10/2018 - 02:55
Yes, it does not need to be complicated. Simply removing the Map type will give you what you need.
User 10/10/2018 - 02:55
yeah i see what you mean
User 10/10/2018 - 02:56
thanks for your help
Excelchat Expert 10/10/2018 - 02:56
You are welcome. I'm glad to have helped clarify it with you.
Excelchat Expert 10/10/2018 - 02:56
Would there be anything else that I can help you with regards to the original question?
Excelchat Expert 10/10/2018 - 02:57
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 10/10/2018 - 02:57
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 10/10/2018 - 02:57
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc