COUNTIF between dates and if value = "y"

[Guest]

I have been using the COUNTIF function to count the occurences of dates that
fall between a particular set of dates.
Is there a way to count these dates, only if a cell on the same row as the
counted date equals 'Y"
This is the formula that I have been using to count occurences between
Dec.31-06 and Dec.31-07.
=COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06")

Can I add to this in order to count the cells that also contain a "y" on the
same row?
Column A are the dates, Column B are the Y or N values.
A B
1 Sept-08-06 Y
2 Oct-24-05 N
3 Jul-21-07 N
4 Aug-10-07 Y
5 Jan-01-05 Y

The date COUNTIF formula shown above would result in 2. (A3 and A4)
The result I am looking for (with the "y") should be 1. (A4 only)

Any ideas ?

Thx.

 

[Peo Sjoblom]

=SUMPRODUCT(--(YEAR(A1:A5)=2007),--(B1:B5="Y"))


--


Regards,


Peo Sjoblom

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A5>=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y"))

Better to use cells to hold the criteria.

D1 = start date
E1 = end date
F1 = Y

=SUMPRODUCT(--(A1:A5>=D1),--(A1:A5<=E1),--(B1:B5=F1))

 

[Peo Sjoblom]

I think that 12/31/06 should be excluded given the countif formula the OP
posted, that means it can be done using YEAR


--


Regards,


Peo Sjoblom

 

[T. Valko]

Yeah, when I see a posted formula like:

=COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06")

I always assume (for better or worse!!!) they meant >= and <=. You know what
they say about assuming!

 

[Guest]

This works quite nicely. Thanks very much.
However, I will need to swap out my COUNTIF formula to the SUMPRODUCT
formula shown below in order to keep my values consistant.
=SUMPRODUCT(--(A1:A5>=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)))

All I did was remove the "Y" array.

Thx again,
JLV

 

[Guest]

Hello,
Just some clarification...
The formula below subtracts all of the dates that are <= 12/31/06 from all
dates that are <= 12/31/07. This will give me a count of all the dates from
01/01/07 to 12/31/07.

I wasn't aware that a conditional like " <= and => " could be used in a
COUNTIF.
If it can be....great.

 

[Peo Sjoblom]

What's wrong with using the YEAR on one array instead of >= and <= on 2
arrays?



--


Regards,


Peo Sjoblom

 

[T. Valko]

I misinterpreted your intentions. Since all you want to do is count entries
for the year 2007 Peo's suggestion is the best solution.

I understand the logic of subtracting <= and <= but it seems more intuitive
(to me) to use >= and <=