Griffin13d
New Member
- Joined
- Oct 30, 2018
- Messages
- 7
Hello, Mr Excel people. I am wondering if either SUMIFS or COUNTIFS are either do not have the ability to do what I need, or if I am just typing things incorrectly to get them to work for my application.
To set up my context. My company works on heavy equipment. There are various user groups for that equipment. We track the equipment that is broken on a spreadsheet and distribute that daily. Instead of manually counting each thing, they want it to auto tally to show how many of each are out of service. I can do that for an entire equipment type, but they now want it automatically separated automatically within the equipment type, to show the user groups as well. The request is to not have to type anything other than the unit number and have the sheet smart enough to decide who it goes to.
I have a master sheet of all of the equipment that I have thrown in the 2nd sheet for it to draw the data from, but I'm not sure if the formula will be smart enough for all of the parameters.
I need it to look at cells E22:E69 (same sheet, and where we type in the broken equipment numbers) and say if they match D6:D10000 on sheet "inventory", and also if the user group in C6:C10000 (on sheet "inventory") is "STNOPS" and the equipment type in F6:F10000 (on sheet "inventory") is "tractor", then to count/tally/add those together to give me a total of the matches. I'd do another one for a different user group, then repeat this for 2 other equipment types for both user groups.
I hope that makes sense. I'm not sure how to get a SUMIFS or COUNTIFS to tally these up as a matching. I know I could get either of them to work within the sheet if someone was to put a designator on the first page as to the user group, but they don't want to do that. so it will just be looking at a column of asset/unit numbers, then comparing them to the other sheet to see when it finds the match with that is the correct user group and correct equipment type, and then count them.
Also, the request is so that the inventory list on the ''inventory'' sheet can be updated and everything will automatically see this and correct the tallies.
Thanks in advance. feel free to ask any clarifying questions. I tried to provide all the info necessary without directly posting screenshots or the spreadsheet itself.
To set up my context. My company works on heavy equipment. There are various user groups for that equipment. We track the equipment that is broken on a spreadsheet and distribute that daily. Instead of manually counting each thing, they want it to auto tally to show how many of each are out of service. I can do that for an entire equipment type, but they now want it automatically separated automatically within the equipment type, to show the user groups as well. The request is to not have to type anything other than the unit number and have the sheet smart enough to decide who it goes to.
I have a master sheet of all of the equipment that I have thrown in the 2nd sheet for it to draw the data from, but I'm not sure if the formula will be smart enough for all of the parameters.
I need it to look at cells E22:E69 (same sheet, and where we type in the broken equipment numbers) and say if they match D6:D10000 on sheet "inventory", and also if the user group in C6:C10000 (on sheet "inventory") is "STNOPS" and the equipment type in F6:F10000 (on sheet "inventory") is "tractor", then to count/tally/add those together to give me a total of the matches. I'd do another one for a different user group, then repeat this for 2 other equipment types for both user groups.
I hope that makes sense. I'm not sure how to get a SUMIFS or COUNTIFS to tally these up as a matching. I know I could get either of them to work within the sheet if someone was to put a designator on the first page as to the user group, but they don't want to do that. so it will just be looking at a column of asset/unit numbers, then comparing them to the other sheet to see when it finds the match with that is the correct user group and correct equipment type, and then count them.
Also, the request is so that the inventory list on the ''inventory'' sheet can be updated and everything will automatically see this and correct the tallies.
Thanks in advance. feel free to ask any clarifying questions. I tried to provide all the info necessary without directly posting screenshots or the spreadsheet itself.
