software

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 quot;cleanquot; as
posssible.

Thanks
Andy

=SUMPRODUCT(--(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))gt;=TO DAY()-30),
--(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))lt;=TO DAY()))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Andy Dquot; lt;Andy gt; wrote in message
...
gt; I would like to be able to count the number of different iteams in one
column
gt; whose dates are less than 30 days older than the current date in another
gt; colunm.
gt;
gt; For example column A contains different names, and column B contains date
of
gt; birth. I would like to show How many people sharing the same name were
born
gt; within the last 30 days.
gt;
gt; Is this possible? I have managed to achieve this with IF and AND functions
gt; but this means I have to use 2 other coulmns to get the results (Counting
gt; True/False results) and I would idealy like the table to remain as quot;cleanquot;
as
gt; posssible.
gt;
gt; Thanks
gt; Andy
Thanks for the quick reply Bob, but isn't this the same as doing:

=COUNTIF(B:B,quot;gt;quot;amp;(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 quot;less than 30 dayquot; dates for each person.

Is that possible?

quot;Bob Phillipsquot; wrote:

gt; =SUMPRODUCT(--(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))gt;=TO DAY()-30),
gt; --(DATE(YEAR(TODAY()),MONTH(B1:B20),DAY(B1:B20))lt;=TO DAY()))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
quot;Andy Dquot; gt; wrote in message
...
gt; Thanks for the quick reply Bob, but isn't this the same as doing:
gt;
gt; =COUNTIF(B:B,quot;gt;quot;amp;(TODAY()-30))

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.gt; This does give me the number of Dates that are less than 30 days old but I
gt; need to break this number down by the names in column A.
gt;
gt; E.G
gt;
gt; A B
gt; Andy 01/Feb/2006
gt; Andy 20/Mar/2006
gt; Bob 04/Jan/2006
gt; Bob 22/Mar/2006
gt; Bob 26/Mar/2006
gt;
gt; I would like the results to say:
gt;
gt; Andy 1
gt; Bob 2
gt;
gt; As this is the number of quot;less than 30 dayquot; dates for each person.
gt;

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

=SUMPRODUCT(--(A1:A20=quot;Andyquot;),--(B1:B20gt;=TODAY()-30),--(B1:B20lt;=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)

quot;Andy Dquot; gt; wrote in message
...
gt; That's great.
gt;
gt; One last thing, I don't know how big the columns are going to be so I
would
gt; like to set this up for the whole of A and B, is this possible?
gt;
gt; if I change the range to A:A and B:B I get a #NUM! error (am I being
thick?)
Bob,

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

Cheers
Andyquot;Bob Phillipsquot; wrote:

gt; No you are not being thick. SUMPRODUCT works on arrays, and as such, arrays
gt; cannot be whole columns, you have to specify the range.
gt;
gt; Just use a range bigger than you will ever need.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Andy Dquot; gt; wrote in message
gt; ...
gt; gt; That's great.
gt; gt;
gt; gt; One last thing, I don't know how big the columns are going to be so I
gt; would
gt; gt; like to set this up for the whole of A and B, is this possible?
gt; gt;
gt; gt; if I change the range to A:A and B:B I get a #NUM! error (am I being
gt; thick?)
gt;
gt;
gt;