Counting rows

[RobertK]

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.

 

[Peo Sjoblom]

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]

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.

 

[Jacob Skaria]

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) to get the
answer.

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

If this post helps click Yes

 

[RobertK]

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)+(ISNUMBER(B1:B10)*(B1:B10<$C$1))>0)*1))