Count 2 columns

[ambcw]

Hi Guys,

Can anyone help me with this please.
My purpose:
Count how many Q1 has been finish,
Count how many Q1 outstanding(blank)
Same as other categories
Category Finish Date
Q1 1 Mar 11
Q2 28 Feb 11
Q3
H1
H2 2 Mar 11
H3
Q1
Q2
Q3

Thanks in advance.
regards
Andre

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

 

[texasalynn]

try this

Excel Workbook
	A	B	C	D	E	F
1	Category	Finish Date			Count of Finished
2	Q1	1-Mar-11			Q1	1
3	Q2	28-Feb-11			Count not finished
4	Q3				Q1	1
5	H1
6	H2	2-Mar-11
7	H3
8	Q1
9	Q2
10	Q3
Sheet3

Excel 2003

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--($A$2:$A$10=E2),--($B$2:$B$10<>""))
F4	=SUMPRODUCT(--($A$2:$A$10=E4),--($B$2:$B$10=0))

 

[Haseeb Avarakkan]

Hello, try

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Category</td><td style="font-weight: bold;;">Finish Date</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">2007+</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">2003-</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Q1</td><td style="text-align: right;;">1 Mar 11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Category</td><td style="font-weight: bold;;">Finished</td><td style="font-weight: bold;;">OutStanding</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Category</td><td style="font-weight: bold;;">Finished</td><td style="font-weight: bold;;">OutStanding</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Q2</td><td style="text-align: right;;">28 Feb 11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Q1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Q3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Q2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">H1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Q3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Q3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">H2</td><td style="text-align: right;;">2 Mar 11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">H1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">H3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">H2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Q1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">H3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Q2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Q3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A$2:$A$10,$E3,$B$2:$B$10,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">$A$2:$A$10,$E3,$B$2:$B$10,""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$10=$I3</font>),--(<font color="Red">$B$2:$B$10>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$10=$I3</font>),--(<font color="Red">$B$2:$B$10=""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Copy down...

 

[ambcw]

Hi Guys,

Thanks for te reply, I will try this. Looks like it's very easy to you.

Can you recommend any book to read about excel? I'm struggling this a lot since my job now is more on reporting for EQ housing rehabilitation here in Christchurch.

Regards
Andre