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
ok
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.