Count based on date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=SUMPRODUCT(--($A$2:$A$6=E2),--ISNUMBER($B$2:$B$6))

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

Amy said:
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
Click to expand...
within a certain date from the date column.
 
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


Amy said:
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
Click to expand...
within a certain date from the date column.
 
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
 
This gives me an answer of zero and 2 should be the answer.

Any ideas? Thanks. Amy
 
This gives me an answer of zero and the answer should be 2.

Any ideas? Thanks, Amy
 
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
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

countifs Function not giving desired result with date criteria 2
Data Conversion - times, dates & rows 5
If Statement 2
time_report_date-wise_employee-wise 6
Modifying a complex date formual to count months 4
Max Min Differnce in a Range 6
separating date and time 5
Count months away betwwen a strat and end date by month 1

Back
Top