I have a spreadsheet with several columns, two of which are dates (say
columns A & E). I want to count the numer of times column A is less than a
certain date (usually the end of a month) plus the number of times column E
is less than today. However, if column A and E are both less than today I
only want to count it once.
--
Robert K

One way


=SUMPRODUCT(--(A2:A20<DATE(2009,9,30)),--(E2:E20<TODAY()))


you can replace DATE(2009,9,30) which is hard coded (in this case eom sep09)
with a cell where you put the date in question

--


Regards,


Peo Sjoblom


"RobertK" wrote in message
...
I have a spreadsheet with several columns, two of which are dates (say
columns A & E). I want to count the numer of times column A is less than
a
certain date (usually the end of a month) plus the number of times column
E
is less than today. However, if column A and E are both less than today I
only want to count it once.
--
Robert K

Thank you, but I'm not quite there, I think my question didn't explain it
that well. If column A meets the criteria it is counted once, if column E
meets the criteria it is only counted it the date in column A has not been
counted but it the date in column A has been coubted the date in column E
isn't.
--
Robert K


"Peo Sjoblom" wrote:

One way


=SUMPRODUCT(--(A2:A20<DATE(2009,9,30)),--(E2:E20<TODAY()))


you can replace DATE(2009,9,30) which is hard coded (in this case eom sep09)
with a cell where you put the date in question

--


Regards,


Peo Sjoblom


"RobertK" wrote in message
...
I have a spreadsheet with several columns, two of which are dates (say
columns A & E). I want to count the numer of times column A is less than
a
certain date (usually the end of a month) plus the number of times column
E
is less than today. However, if column A and E are both less than today I
only want to count it once.
--
Robert K

Use a helper column say D with the below formula in cell D1.Copy the formula
down as required and once done you can sum that up =SUM(D:D) to get the
answer.

=IF(COUNTIF(A1:B1,"<" & $C$1),1,0)

If this post helps click Yes
---------------
Jacob Skaria


"RobertK" wrote:

Thank you, but I'm not quite there, I think my question didn't explain it
that well. If column A meets the criteria it is counted once, if column E
meets the criteria it is only counted it the date in column A has not been
counted but it the date in column A has been coubted the date in column E
isn't.
--
Robert K


"Peo Sjoblom" wrote:

One way


=SUMPRODUCT(--(A2:A20<DATE(2009,9,30)),--(E2:E20<TODAY()))


you can replace DATE(2009,9,30) which is hard coded (in this case eom sep09)
with a cell where you put the date in question

--


Regards,


Peo Sjoblom


"RobertK" wrote in message
...
I have a spreadsheet with several columns, two of which are dates (say
columns A & E). I want to count the numer of times column A is less than
a
certain date (usually the end of a month) plus the number of times column
E
is less than today. However, if column A and E are both less than today I
only want to count it once.
--
Robert K

Thank you but I'm trying to avoid extra columns since the data runs over 1000
rows. I did get another suggestion from a different post that worked.
=SUMPRODUCT(((ISNUMBER(A1:A10)*(A1:A10<$C$1)+(ISNU MBER(B1:B10)*(B1:B10<$C$1))0)*1))

--
Robert K


"Jacob Skaria" wrote:

Use a helper column say D with the below formula in cell D1.Copy the formula
down as required and once done you can sum that up =SUM(D:D) to get the
answer.

=IF(COUNTIF(A1:B1,"<" & $C$1),1,0)

If this post helps click Yes
---------------
Jacob Skaria


"RobertK" wrote:

Thank you, but I'm not quite there, I think my question didn't explain it
that well. If column A meets the criteria it is counted once, if column E
meets the criteria it is only counted it the date in column A has not been
counted but it the date in column A has been coubted the date in column E
isn't.
--
Robert K


"Peo Sjoblom" wrote:

One way


=SUMPRODUCT(--(A2:A20<DATE(2009,9,30)),--(E2:E20<TODAY()))


you can replace DATE(2009,9,30) which is hard coded (in this case eom sep09)
with a cell where you put the date in question

--


Regards,


Peo Sjoblom


"RobertK" wrote in message
...
I have a spreadsheet with several columns, two of which are dates (say
columns A & E). I want to count the numer of times column A is less than
a
certain date (usually the end of a month) plus the number of times column
E
is less than today. However, if column A and E are both less than today I
only want to count it once.
--
Robert K