Day only formula - Not day & Time?

[Magee]

I have a day & time in Cell C1, but I would like to create a sumproduct formula to count the cells that Geater than the DAY only -

=SUMPRODUCT(--('WIP DATA'!$F$1:$F$99999>0),--('WIP DATA'!$C$1:$C$99999>$C$1),--(ISNUMBER(SEARCH(A31,'WIP DATA'!$F$1:$F$99999))))

Unfortunatly this formula only shows the items greater than the day & Time.

Any suggestions.

 

[njimack]

Maybe this:
=SUMPRODUCT(--('WIP DATA'!$F$1:$F$99999>0),--(DAY('WIP DATA'!$C$1:$C$99999)>DAY($C$1)),--(ISNUMBER(SEARCH(A31,'WIP DATA'!$F$1:$F$99999))))

 

[jim may]

Excel Workbook
	A	B	C	D	E
1			2/22/2011 11:15		2/22/2011
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C1	=NOW()
E1	=INT(C1)

 

[Magee]

Hi Niel,

Thanks, but I just noticed that I didn't explain properly. My fault, Sorry!

I should have said the Date, not Day.

i.e.
C1 = 15/02/2011 15:13

The Range contains:

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=110 border=0><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 83pt; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" width=110 height=19>14/02/2011 08:01</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>15/02/2011 16:38</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>14/02/2011 09:23</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>10/02/2011 12:31</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>15/02/2011 10:31</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>16/02/2011 10:44</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl63 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #e0e0e0 1pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #f7f7f7" height=19>16/02/2011 11:49</TD></TR></TBODY></TABLE>

I would like to show only the 16/02/2011 (2) that are greater than C1 (not the 15/02/16:38)

I would also like to count how many are dated the 15/02/2011 (C1).

 

[barry houdini]

Instead of >$C$1 in your original formula change to

>=INT($C$1)+1

 

[njimack]

In that case, use a combination of my solution and Jim's:
=SUMPRODUCT(--('WIP DATA'!$F$1:$F$99999>0),--(INT('WIP DATA'!$C$1:$C$99999)>INT($C$1)),--(ISNUMBER(SEARCH(A31,'WIP DATA'!$F$1:$F$99999))))

Incidentally, do you really need to sum 100,000 rows? A dynamic range may be more efficient: http://contextures.com/xlNames01.html#Dynamic

 

[Magee]

Hi Neil,

It replies a #VALUE ?

Simon

 

[barry houdini]

Did you try my suggestion?

 

[Magee]

Thanks Barry, that have sorted my first problem, thank you.