Preparing Data for Stacked Column Chart in Excel

I recently had a situation where I have part measured repeatedly after rework. The number of times the each parts measured and which  measurement belongs to which rework is only can be identified by "time stamp" (the time of the measurement). Please refer to the sample data. Please note the data is sort by time stamp and part name. 

Now I need to a Stacked Column Chart like below. This is to have visual representation of the effect of rework. My original intention to use Qliksense or Tableau but excel can do the job with an additional table. 

Step 1: First sort the table by part and then by time stamp. 

Step 2: Add a column (column D) with the formula

="Measurement No" & COUNTIF(B$2:B2,B2)

Step 3: Drag the formula all the way.

Step 4: Now you can create the table for the Stacked Column Chart.

The table should be like the image below.

Add formula =SUMIFS($C:$C,$B:$B,J$1,$D:$D,$I2) in cell J2.

Fill the table with the formula.

Use insert → chart → stacked column to create the chart.

SUMIF, SUMIFS and SUMPRODUCT

Let's look at the example below.

Zone	Shift	No of  Machines	Output per machine
Zone 1	Shift A	15	15
Zone 2	Shift A	15	15
Zone 1	Shift B	15	20
Zone 2	Shift B	15	20
Zone 3	Shift A	15	20
Zone 1	Shift B	20	20
Zone 3	Shift B	20	15
Zone 3	Shift B	20	20
Zone 1	Shift A	20	20
Zone 2	Shift B	15	20

The requirement is to calculate the output for Zone 1 for Shift B.

SUMIF

1. Using SUMIF would require two additional columns to be created.

2. One to CONCATENATE to Zone and Machine =CONCATENATE(I2,J2) and another to multiply column K and L =K2*L2

3. Then we can use ==SUMIF(M2:M11,"Zone 1Shift B",N2:N11)

SUMIFS

1. Using SUMIFS will require one additional column to be created.

2. Add a Column to multiply column K and L =K2*L2 (column M lo longer required).

3. Then we can use =SUMIFS(N2:N11,I2:I11,"Zone 1",J2:J11,"Shift B")

SUMPRODUCT

1. Using SUMPRODUCT will NOT require any more additional columns.

2. SUMPRODUCT is meant for "Multiplies corresponding components in the given arrays, and returns the sum of those products."

3. IF we use =SUMPRODUCT(I2:I11="Zone 1",J2:J11="Shift B",K2:K11,L2:L11) the answer will be "0". It is because the results of 

I2:I11="Zone 1" = {TRUE, FALSE,TRUE, FALSE,FALSE....}

J2:J11="Shift B" = {FALSE,FALSE,TRUE,TRUE,FALSE.....}

4. To change TRUE  into 1 and FALSE into 0, we need to use "--". 

=SUMPRODUCT(--(I2:I11="Zone 1"),--(J2:J11="Shift B"),K2:K11,L2:L11)

--(I2:I11="Zone 1") = {1;0;1;0;0;.....}
--(J2:J11="Shift B") = {0;0;1;1;0......}

Note: 

-(I2:I11="Zone 1") = {-1;0;-1;0;0;.....}
-(J2:J11="Shift B") = {0;0;-1;-1;0......}


5. Essentially the calculation is,

(1x0x15x15 + 0x0x15x15 + 1x1x15x20 +........)

6. This formula will give answer we need.

Love to have your comments....

How to use SUMIFS

In an earlier post  I discussed about using CONCATENATE and SUMIF to sum with two conditions. This can be done using the new SUMIFS command.

lets look at the following example, if I need to know the quantity produced by machine 9 on date 4th Jan 2011.

Use formula

=SUMIFS(H:H,F:F,DATE(2011,1,4),G:G,9)

Done.