How to plot chart by showing continuous month data

[vibinvenugopal]

Hi All,

Please see data below.

Employee #	Employee Name	Designation	Senior/Junior	Allocation Start Date	Allocation End Date
123	ABC	Engineer	J	1-Apr-14
456	DEF	Engineer	J	1-Apr-14
789	GHI	Manager	J	1-Apr-14
112	JKL	manager	J	1-Jul-14

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I wanted to plot a bar chart where it shows the allocation start date ( as month) on the X axis and on the Y axis it should show the number of people who have allocation start date for that month. So for eg as per the data in the april month there are 3 people, and it should show may and june and july etc as 3 since there is no allocation end date for those employees.

 

[Steve=True]

Hi Vibinvenugopal,

You will have to create a chart data area to compile the data. Below are sumproduct formulas that will work.

Also, I would suggest that you chart the data from I1:U3 as a Clustered Column Chart instead of a Bar Chart as it will make more sense.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Employee #	Employee Name	Designation	Senior/Junior	Allocation Start Date	Allocation End Date		Chart Data		4/1/2014	5/1/2014	6/1/2014	7/1/2014	8/1/2014	9/1/2014	10/1/2014	11/1/2014	12/1/2014	1/1/2015	2/1/2015	3/1/2015
2	123	ABC	Engineer	J	1-Apr-14				Engineer	2	2	2	2	2	1	1	1	1	1	1	1
3	456	DEF	Engineer	J	1-Apr-14	8/1/2014			Manager	1	1	1	2	2	2	2	1	1	1	1	1
4	789	GHI	Manager	J	1-Apr-14
5	112	JKL	manager	J	1-Jul-14	10/1/2014
Sheet1

Cell Formulas
Range	Formula
J2	=SUMPRODUCT(($I2=$C$2:$C$5)*(J$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(J$1>$F$2:$F$5))
J3	=SUMPRODUCT(($I3=$C$2:$C$5)*(J$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(J$1>$F$2:$F$5))
K2	=SUMPRODUCT(($I2=$C$2:$C$5)*(K$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(K$1>$F$2:$F$5))
K3	=SUMPRODUCT(($I3=$C$2:$C$5)*(K$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(K$1>$F$2:$F$5))
L2	=SUMPRODUCT(($I2=$C$2:$C$5)*(L$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(L$1>$F$2:$F$5))
L3	=SUMPRODUCT(($I3=$C$2:$C$5)*(L$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(L$1>$F$2:$F$5))
M2	=SUMPRODUCT(($I2=$C$2:$C$5)*(M$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(M$1>$F$2:$F$5))
M3	=SUMPRODUCT(($I3=$C$2:$C$5)*(M$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(M$1>$F$2:$F$5))
N2	=SUMPRODUCT(($I2=$C$2:$C$5)*(N$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(N$1>$F$2:$F$5))
N3	=SUMPRODUCT(($I3=$C$2:$C$5)*(N$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(N$1>$F$2:$F$5))
O2	=SUMPRODUCT(($I2=$C$2:$C$5)*(O$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(O$1>$F$2:$F$5))
O3	=SUMPRODUCT(($I3=$C$2:$C$5)*(O$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(O$1>$F$2:$F$5))
P2	=SUMPRODUCT(($I2=$C$2:$C$5)*(P$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(P$1>$F$2:$F$5))
P3	=SUMPRODUCT(($I3=$C$2:$C$5)*(P$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(P$1>$F$2:$F$5))
Q2	=SUMPRODUCT(($I2=$C$2:$C$5)*(Q$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(Q$1>$F$2:$F$5))
Q3	=SUMPRODUCT(($I3=$C$2:$C$5)*(Q$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(Q$1>$F$2:$F$5))
R2	=SUMPRODUCT(($I2=$C$2:$C$5)*(R$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(R$1>$F$2:$F$5))
R3	=SUMPRODUCT(($I3=$C$2:$C$5)*(R$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(R$1>$F$2:$F$5))
S2	=SUMPRODUCT(($I2=$C$2:$C$5)*(S$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(S$1>$F$2:$F$5))
S3	=SUMPRODUCT(($I3=$C$2:$C$5)*(S$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(S$1>$F$2:$F$5))
T2	=SUMPRODUCT(($I2=$C$2:$C$5)*(T$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(T$1>$F$2:$F$5))
T3	=SUMPRODUCT(($I3=$C$2:$C$5)*(T$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(T$1>$F$2:$F$5))
U2	=SUMPRODUCT(($I2=$C$2:$C$5)*(U$1>=$E$2:$E$5))-SUMPRODUCT(($I2=$C$2:$C$5)*($F$2:$F$5>0)*(U$1>$F$2:$F$5))
U3	=SUMPRODUCT(($I3=$C$2:$C$5)*(U$1>=$E$2:$E$5))-SUMPRODUCT(($I3=$C$2:$C$5)*($F$2:$F$5>0)*(U$1>$F$2:$F$5))

Here is a picture of the results:

Hope this helps.


Steve=True

 

[vibinvenugopal]

Thanks Steve, this really worked. I had a hard time cracking this problem, but your answer really helped me and I was able to plot the chart. Thanks so much once again!

Vibin

 

[Steve=True]

You are very welcome. Glad it worked and made sense.

Steve=True