Count if dates meet certain criteria

[RobertK]

I have a spreadsheet similar to this. I need to count the number of dates in
column A that are less than C1. If the date in column A is greater than C1
than I want to count the date in column B if it is less than C1. If the date
in column A & B are both less than C1 they should count as 1 and not 2. In
the example below 9 dates meet the criteria (8 in column A and 1 in column
B). Also the database length will vary and will contain blank cells after
the last entry in column A.

A B C
1/2/2009 3/2/2008 9/16/2009
3/2/2009 6/5/2008
6/5/2008 9/4/2008
3/1/2010
1/1/2010 4/1/2009
7/5/2009
9/15/2008 9/15/2009
12/4/2008
11/4/2008



2/12/2009
3/12/2010

 

[Ms-Exl-Learner]

I am confused...

Please check the data and your query once again.

Your Wordings:-
In the example below 9 dates meet the criteria (8 in column A and 1 in
column B)

But if you see the example once again you will notice that two dates in A
column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So
the Number of dates greater than C1 count is 2.

Your Wordings:-
In the example below 9 dates meet the criteria (8 in column A and 1 in
column B)

I dont know how you have calculated one date in Column B because all the
dates are less that c1 cell.

Please once again explain your query with clear picture.

If you want to count the A&B Column values which is GREATER than C1 cell
then use this formula:
=COUNTIF(A:A,">"&$C$1)+COUNTIF(B:B,">"&$C$1)

If you want to count the A&B Column values which is LESS than C1 cell then
use this formula:
=COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)

If this post helps, Click Yes!

 

[RobertK]

The countif formula works fine except that if the date in column A and the
date in column B are both less than C1, I don't want to count it each time.
I only want to count it as one. Does this explanation help.

 

[Ms-Exl-Learner]

Use this formula for finding the values in A&C Column which is GREATER than
C1 Cell:
=IF(COUNTIF(A:A,">"&$C$1)+COUNTIF(B:B,">"&$C$1)<=0,"1",COUNTIF(A:A,">"&$C$1)+COUNTIF(B:B,">"&$C$1))


Use this for formula for finding the values in A&C Column which is LESS than
C1 Cell:
=IF(COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)<=0,"1",COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1))

If this post helps, Click Yes!

 

[Ms-Exl-Learner]

Both the formulas can be reduced and the same is given below:-

Use this formula for finding the values in A&B Column which is GREATER than
C1 Cell:-
=IF(COUNTIF(A:B,">"&$C$1)<=0,"1",COUNTIF(A:B,">"&$C$1))


Use this for formula for finding the values in A&B Column which is LESS than
C1 Cell:-
=IF(COUNTIF(A:B,"<"&$C$1)<=0,"1",COUNTIF(A:B,"<"&$C$1))

If this post helps, Click Yes!

 

[RobertK]

Sorry, I;m still not explaining this correctly. The formula you sent counts
all the dates in column A and B that are less than C1. I only want to count
one date per row that is less than C1.

A B C
1/2/2009 3/2/2009 9/15/09
3/2/2010 6/5/2008

In the example the first date in column A meets the criteria and both dates
in column B meet the criteria. In this case the answer I'm looking for is 2.
The 1/2/09 in column A and the 6/5/08 in column B. The 3/2/09 date is
ignored in column B since the 1/2/09 date in column A has been counted. in
other words only count one date per row that meets the criteria. Thanks,

 

[Ms-Exl-Learner]

Robert,

Both (i.e.) A1 & B2 are less than c1 then you need to display the value as
2, If A1 or B2 is greater than C1 then what would you like to show?

 

[Jacob Skaria]

Hi Robert

You can try the below formula. Please note that this is an array formula. An
array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=SUM((IF(A1:A100>0,IF(A1:A100<C1,1,IF(B1:B100>0,IF(B1:B100<C1,1,0))))))

If this post helps click Yes

 

[Joerg Mochikun]

Try
=SUMPRODUCT(((ISNUMBER(A1:A10)*(A1:A10<$C$1)+(ISNUMBER(B1:B10)*(B1:B10<$C$1))>0)*1))

The formula counts the cases where either the date in col A or B is less
than C1. It ignores blank cells.

Cheers,
Joerg Mochikun

 

[RobertK]

Thank You that did the trick.
--
Robert K

Try
=SUMPRODUCT(((ISNUMBER(A1:A10)*(A1:A10<$C$1)+(ISNUMBER(B1:B10)*(B1:B10<$C$1))>0)*1))

The formula counts the cases where either the date in col A or B is less
than C1. It ignores blank cells.

Cheers,
Joerg Mochikun

 

[RobertK]

Thank you, that did the trick.
--
Robert K

Hi Robert

You can try the below formula. Please note that this is an array formula. An
array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=SUM((IF(A1:A100>0,IF(A1:A100<C1,1,IF(B1:B100>0,IF(B1:B100<C1,1,0))))))

If this post helps click Yes