How do I count differert items in a column only if the date in an.

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

Guest

I would like to be able to count the number of different iteams in one column
whose dates are less than 30 days older than the current date in another
colunm.

For example column A contains different names, and column B contains date of
birth. I would like to show How many people sharing the same name were born
within the last 30 days.

Is this possible? I have managed to achieve this with IF and AND functions
but this means I have to use 2 other coulmns to get the results (Counting
True/False results) and I would idealy like the table to remain as "clean" as
posssible.

Thanks
Andy
 
=SUMPRODUCT(--(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))>=TODAY()-30),
--(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))<=TODAY()))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thanks for the quick reply Bob, but isn't this the same as doing:

=COUNTIF(B:B,">"&(TODAY()-30))

This does give me the number of Dates that are less than 30 days old but I
need to break this number down by the names in column A.

E.G

A B
Andy 01/Feb/2006
Andy 20/Mar/2006
Bob 04/Jan/2006
Bob 22/Mar/2006
Bob 26/Mar/2006

I would like the results to say:

Andy 1
Bob 2

As this is the number of "less than 30 day" dates for each person.

Is that possible?
 
Andy D said:
Thanks for the quick reply Bob, but isn't this the same as doing:

=COUNTIF(B:B,">"&(TODAY()-30))
Click to expand...

No it isn't. I assuemd that you were enteriung birth days. not tjis year's
birthday, so I catered for that. And yours also includes future dates.

This does give me the number of Dates that are less than 30 days old but I
need to break this number down by the names in column A.

E.G

A B
Andy 01/Feb/2006
Andy 20/Mar/2006
Bob 04/Jan/2006
Bob 22/Mar/2006
Bob 26/Mar/2006

I would like the results to say:

Andy 1
Bob 2

As this is the number of "less than 30 day" dates for each person.
Click to expand...

Cannot see why you would want to do that, but it's your spreadsheet

=SUMPRODUCT(--(A1:A20="Andy"),--(B1:B20>=TODAY()-30),--(B1:B20<=TODAY()))
 
That's great.

One last thing, I don't know how big the columns are going to be so I would
like to set this up for the whole of A and B, is this possible?

if I change the range to A:A and B:B I get a #NUM! error (am I being thick?)
 
No you are not being thick. SUMPRODUCT works on arrays, and as such, arrays
cannot be whole columns, you have to specify the range.

Just use a range bigger than you will ever need.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Andy D said:
That's great.

One last thing, I don't know how big the columns are going to be so I would
like to set this up for the whole of A and B, is this possible?

if I change the range to A:A and B:B I get a #NUM! error (am I being
Click to expand...
thick?)
 
Bob,

Just wanted to say thanks very much for your help, my spreadsheet now does
exactly what I wanted.

Cheers
Andy
 
Back
Top