Advanced filtering with sum and zero values

[javedmati]

Hi,

I am creating an excel for tracking the performance of my Sales folks.

In one of the sheets I am entering the data the following way.

<title>Excel Jeanie HTML</title>INPUT

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:110px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="background-color:#000000; color:#ffffff; ">Employee Name</td><td style="background-color:#000000; color:#ffffff; ">Month</td><td style="background-color:#000000; color:#ffffff; ">Amount</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>EMP1</td><td>April</td><td style="text-align:right; ">100000</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>EMP4</td><td>April</td><td style="text-align:right; ">54000</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>EMP1</td><td>April</td><td style="text-align:right; ">75000</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>EMP5</td><td>May</td><td style="text-align:right; ">25000</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>EMP3</td><td>July</td><td style="text-align:right; ">150000</td></tr></tbody></table>
I want the following output.

<title>Excel Jeanie HTML</title>OUTPUT

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:110px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:76px;"><col style="width:57px;"><col style="width:73px;"><col style="width:71px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="background-color:#000000; color:#ffffff; ">Employee Name</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">April</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">May</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">June</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">July</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">August</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">September</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">October</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">November</td><td style="background-color:#000000; color:#ffffff; text-align:center; ">December</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:center; ">EMP1</td><td style="text-align:center; ">175,000</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:center; ">EMP2</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:center; ">EMP3</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">150,000</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:center; ">EMP4</td><td style="text-align:center; ">54,000</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:center; ">EMP5</td><td style="text-align:center; ">0</td><td style="text-align:center; ">25,000</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B11</td><td>=C2+C4</td></tr></tbody></table></td></tr></tbody></table>
So basically, whether the person has done the sales or not, I want to extract the summary for all 5 employees for the whole year.

Please feel free to ask if you need any clarifications.

Always appreciate your time

Regards,
Javed

 

[shadow12345]

have you looked at pivot tables? that would seem to be the best way to do that.

*you might have to list all the months at least once, but you could leave the values a zero anyway

 

[javedmati]

Hi,

Yes I have tried PIVOT. But the problem is exactly the point which you mentioned that I need to list all the months at least once. Actually the data is entered as and when the SALE happens so at any given time not all months are present.

Regards,
Javed