Count based on date

[Amy]

I have a column of dates and a column of currency.

How do I count the number of cells that contain a value if they fall within a certain date from the date column.

For example:

Date Value
6/15/04 10.00
6/15/04
6/15/04 20.00
6/16/04 15.00
6/16/04

For 6/15/04 I have a total of 2 values and for 6/16/04 I have 1 value.

Any ideas? Thanks. Amy

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$6=E2),--ISNUMBER($B$2:$B$6))

where E2 houses a date condition like 6/15/04 (15-Jun-04).

I have a column of dates and a column of currency.

How do I count the number of cells that contain a value if they fall

within a certain date from the date column.

 

[Peo Sjoblom]

One way

=COUNTIF(A1:A10,DATE(2004,6,15))

should be pretty self-explanatory

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

I have a column of dates and a column of currency.

How do I count the number of cells that contain a value if they fall

within a certain date from the date column.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100=DATE(2004,6,15)),--(B1:B100<>""))

 

[Peo Sjoblom]

Sorry, I didn't read your question thoroughly enough, see Aladin's and
Frank's answers

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Amy]

This gives me an answer of zero and 2 should be the answer.

Any ideas? Thanks. Amy

 

[Amy]

This gives me an answer of zero and the answer should be 2.

Any ideas? Thanks, Amy

 

[Peo Sjoblom]

Your dates are text and not numeric dates, what happens if you use

=ISTEXT(A2)

if you get TRUE it is text, where do the dates come from (imported etc)?
Copy an empty cell, select the dates and do edit>paste special and check add

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Amy]

I get FALSE. The column is formatted as a date and we enter them directly. They have not be imported from a different source and/or format.

 

[Frank Kabel]

Hi
see Aladin's response. Probably a formating issue