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

1. ## 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!  Register To Reply

2. ## 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:  `Please Login or Register  to view this content.`  Register To Reply

3. ## 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)  Register To Reply

4. ## 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.  Register To Reply

5. ## 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  Register To Reply

6. ## 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!  Register To Reply

7. ## 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.  Register To Reply