+ Reply to Thread
Results 1 to 7 of 7

Find specific values and multiply them by a number in their column

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Find specific values and multiply them by a number in their column

    Hey everybody,

    I have a question. In my example of data of a production process every batch has to go through 6 operations. For every batch each operation is performed by a different machine, numbered 1 to 10. A zero indicates that batch didn't need to go through that operation.

    I need to know how many operations each machine performs. For example: In batch 14, machine 10 performs 3 operations so the total operations is 3 * 39 (batch size) = 117. In the end I need a total of the operations each machine performs over all batches.

    Is there a formula or series of steps I can take to make this easier for the way larger document I have to analyse?

    Thanks!
    Attached Files Attached Files
    Last edited by Thomas92W; 04-29-2014 at 08:10 AM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find specific values and multiply them by a number in their column

    Hello

    I suggest to use 2 validation lists to choose batch and machine and then this formula to get your result.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Find specific values and multiply them by a number in their column

    in J1 to S1 Type numbers from 1 to 10
    then in J2 copy paste below then hold control and shift then hit enter to make it array formula.
    =SUM(MMULT(--(TRANSPOSE($C2:$H2=J$1)),$B2))

    Then drag down and drag to right

    another way copy paste below in J2 and hit enter drag down

    =SUMPRODUCT((C15:H15=J1)*B15)
    Last edited by hemesh; 04-29-2014 at 08:23 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    04-29-2014
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Find specific values and multiply them by a number in their column

    Thank you Fotis1991 and hemesh,

    Fotis1991, this works but I need a list to show each machine's workload. With over 10,000 batches and 50 machines it would be too much work to manually do this for each.

    Hemesh, the first formula immediately gives an error without any additional information than the fact it contains an error.

    The second formula seems to work (except I changed it to: =SUMPRODUCT((C2:H2=J1)*B2) when I pasted it in J2) but it doesn't account for the fact that some machines feature in two operations in the same batch. The machine's have twice the workload when they perform two operations on the same batch.

    Also, when I drag it down it also changes the J1 into J2 and then J3 when I drag it down. I am fairly new to excel, is there an easy way to choose which values it automatically increases and which it doesn't?

    Again, thank you guys, you've already helped me a lot.

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Find specific values and multiply them by a number in their column

    I updated second formula wrongly I was checking for the batch No.14

    And MMULT needed to be entered as array formula. find attached
    Attached Files Attached Files
    Last edited by hemesh; 04-29-2014 at 09:03 AM.

  6. #6
    Registered User
    Join Date
    04-29-2014
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Find specific values and multiply them by a number in their column

    Thank you Hemesh, I finished it with the help of your last post. Greatly appreciated!

  7. #7
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Find specific values and multiply them by a number in their column

    You are welcome Thomas, if this takes care of your original question then you can mark the thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to multiply specific values from two tables
    By ayresm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-13-2013, 05:07 PM
  2. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  3. Replies: 4
    Last Post: 05-26-2013, 05:54 PM
  4. Find and show sum of values that equal a specific number
    By nplouffe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2011, 12:07 PM
  5. Find column number that has a specific value
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2011, 05:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1