I want to match machine number and operation to the list of days below to sum the operation hours each day. Please see the attached.
Machine numbers will pair with the operations (M,E,S,...) to return the summed values of each for the day. All M values will be summed if shown in the day for each machine, the same goes for E and so on.
Last edited by anderem12; 12-19-2019 at 10:59 AM.
Once again your sample is VERY obscure and lacks a detailed descriptoion of what you want.
How does Excel know which column to select from M119017 or M119018?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Is this, in any way, different from your other recent query?
The Machine numbers are matched from the column to the row that is shown. I will be adding in other machine numbers and more operation data. The formula you provided was not flexible enough for that.
I didn't offer any solutions. I didn't understand yoru request then. I think I do now. I am just wondering why you opened a parallel thread. See rule 4. No duplicate threads....
I apologize, I marked the thread as solved, and it is different because the other sheet hadn't involved calculated numbers, just inputted values. This has a lot of other values and formulas to it, all of it is hidden.
Some progress is being made. I have it working for one machine at a time!!
How many machines are there?
It will not have a set number, a variation of machines can be added at a time, so it would need to be dynamic enough to handle more.
Don't hold your breath while you're waiting!
Okay, thanks.
I asked in your other thread (hopefully, now abandoned), if you are likely to have more than 2 letters in the cells on rows 24 and 25. I meant the letters M, E, S, B etc. which begin in column C and appear across the sheet. Looking at your data these seem to occur either on their own, or in pairs, but in fact this solution will cope if there are more than 2 letters in a cell.
The other variable, which you have mentioned above, is the number of machines that you might have. I will show you later what you need to do to the formula if more machines are added.
You can get the answers that you show using this formula in cell C35:
=IF(ISNUMBER(SEARCH($A35,C$24)),VLOOKUP($A35,$T$2:$V$14,MATCH($A$24,$T$1:$V$1,0),0),0) + IF(ISNUMBER(SEARCH($A35,C$25)),VLOOKUP(LEFT($A35),$T$2:$V$14,MATCH($A$25,$T$1:$V$1,0),0),0)
and this can be copied across and down, as required. You can see that I have put spaces on either side of the + sign in the middle. The formula is in 2 parts, as you have 2 machines in this file, but if you had 3 machines then you would have another + sign at the end followed by similar terms, i.e. you would copy the blue-coloured part and paste it at the end, and then change the red-coloured parts to suit the row number where the new machine exists (e.g. to row 26). You will also have to add a new column to your table in T to V with details for the new machine in column W, and that would mean changing the references to column V (in green) to W instead. The formula would then need to be copied across and down again.
Hope this helps.
Pete
C35=SUMPRODUCT(SUMIFS(INDEX($U$2:$V$2,MATCH($A35,$T$2:$T$14,0),),$U$1:$V$1,$A$24:$A$25)*ISNUMBER(SEARCH($A35,C$24:C$25)))
Try this, Copy and across
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi, Samba. That's great!!! One minor tweak to make it copyable down as well as across:
=SUMPRODUCT(SUMIFS(INDEX($U$2:$V$14,MATCH($A35,$T$2:$T$14,0),),$U$1:$V$1,$A$24:$A$25)*ISNUMBER(SEARCH($A35,B$24:B$25)))
Thank you sir for correcting me.Originally Posted by Glenn Kennedy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks