COUNTA with IF function

[UberFrank]

I have two columns of data, Colums A containing a list of dates in July and Column B containing either data or no data. I need to return the number of Rows in B that contain data for each day in the month.

Have tried sum product to no avail as well as diferent combinations of COUNTA with IF and COUNTIF. Any idea?

Many thanks in advance.

 

[Matty]

Perhaps like:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 19px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Date</TD><TD>Data</TD><TD></TD><TD>Date</TD><TD>Data Count</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD>Data1</TD><TD></TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD>Data2</TD><TD></TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD>Data3</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">01/07/2010</TD><TD>Data5</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD>Data7</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD>Data8</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD>Data9</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">02/07/2010</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=SUMPRODUCT(--(A$2:A$11=D2),--(B2:B11<>""))</TD></TR><TR><TD>E3</TD><TD>=SUMPRODUCT(--(A$2:A$11=D3),--(B3:B12<>""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Matty

 

[Peter_SSs]

If the data is (or can be) sorted by date, what about using Excel's built-in Subtotal feature?

Excel Workbook
	A	B
1	Date	Data
2	01/07/2010	Data1
3	01/07/2010	Data2
4	01/07/2010	Data3
5	01/07/2010
6	01/07/2010	Data5
7	01/07/2010 Count	4
8	02/07/2010
9	02/07/2010	Data7
10	02/07/2010	Data8
11	02/07/2010	Data9
12	02/07/2010
13	02/07/2010 Count	3
14	Grand Count	7
Sub Total

 

[UberFrank]

Both work perfectly, Matty that was exactly what i was after.


Thank you both

The Frank

 

[Peter_SSs]

Another built-in feature (Pivot Tbale) would also be perfect for this (even if the data is not sorted by date). The Pivot Table formatting does not show that well in Excel jeanie but would be fine on your sheet.

Excel Workbook
	A	B	C	D	E	F
1	Date	Data		Count of Data
2	01/07/2010	Data1		Date	Total
3	02/07/2010	Data8		01/07/2010	4
4	01/07/2010	Data3		02/07/2010	3
5	02/07/2010	Data9		Grand Total	7
6	01/07/2010	Data5
7	02/07/2010
8	01/07/2010
9	02/07/2010	Data7
10	01/07/2010	Data2
11	02/07/2010
12
Sub Total