Prerequisite
Know how to use "CONCATENATE" and "SUMIF" individually. Please refer to Microsoft help for
Syntax.
What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.
Simple Example
Lets consider a simple scenario,
Data given (raw data) : quantity produced by different machines on different days
Data needed : mapping (matrix) qty produced by different machines on different days.
The steps (How to do)
Stap 1: Create a new column to join date and machine using "Concatenate".
Note I am using "-" to make sure no duplicates.
the formula in column (refer to the image) is =CONCATENATE(N3,"-",O3).
Make sure the formula is dragged to all the way to the bottom of the table.
Step 2: create the matrix
Manufacturing date in the column and machines in the raw (it can be done vise-verse also)
Step 3: formula to calculate the total quantity produced by each machines on different days.
Use sumif to do the calculation. Please note the range column and sum_range column are different. Lets start in cell B3.
range Required. Column M (where the concatenate values are)
criteria Required. CONCATENATE($A3,"-",B$2)
sum_range Required . Column P
range Required. Column M (where the concatenate values are)
criteria Required. CONCATENATE($A3,"-",B$2)
sum_range Required . Column P
Let's focus on CONCATENATE($A3,"-",B$2). This formula is the same as in column M. The main difference in freezing column and raw values. $A3 is column freeze and B$2 is raw freeze. The freezing allow us to drag the formula.
Step 4: Drag the formula to fill up the entire table
DONE
Step 5: Check and Balance
Always good to do a check on the accuracy.In this case SUM(B3:J12) sould be equal to SUM(P:P)
This is my first post in this blog let me know your thoughts.
No comments:
Post a Comment