Conditional Counting with Dates

[TRPlace]

Hello,
I have been working on this all day. I am trying to count only if two conditions are met. The first is a date range (I think this is where my problems are coming in, maybe because Excel stores dates as serial numbers?), and the second condition is if a cell has certain text.

if the date is in column A:A is >=1/1/2008<=1/19/2008 and F:F="Austin"

I've been able to get part of it work, =COUNTIF('JAN 08'!F:F,"Austin"), this returns the number of times Austin is displayed F:F

and this works to read one specific value =COUNTIF('JAN 08'!A:A,"1/17/2008"), this works (working with Excel's serial numbers for dates) =IF('JAN 08'!A3>=39446,"YES","NO"), it returns YES

I've used countif, I've tried sumproduct('JAN 08'!>=date(2008,1,1),'JAN 08'!A:A<=date(2008,1,13),'JAN 08'!F:F="Austin")

I've played around with the AND function and sum(if(, sumif(, if(and(

If anyone can please help!

Thank you!!

 

[iggydarsa]

=SUMPRODUCT(--($A$1:$A$65535>=DATE(2008,1,1)), --($A$1:$A$65535<=DATE(2008,1,19)), --($F$1:$F$65535="Austin"))

 

[TRPlace]

Thank you so much. I was on the right track but you came through like it was every day knowledge. I really appreciate it. I notice that it is counting dates within that range AND "Austin", I need it to only count Austin within that date range. Is there a way to get that to work?

 

[iggydarsa]

=SUMPRODUCT(--($A$1:$A$65535>=DATE(2008,1,1)), --($A$1:$A$65535<=DATE(2008,1,19)), --($F$1:$F$65535="Austin"))

I need it to only count Austin within that date range

this counts [only] "Austin"s within the range of "1 Jan 08" and "19 Jan 08", isnt what you want?

 

[TRPlace]

My mistake! You're right, it does. My spreadsheet was a little unorganized. Do you have a paypal account? I'd like to send over a few dollars for my appreciation. I've been working on this all day and you solved it in no time!

 

[iggydarsa]

My mistake! You're right, it does. My spreadsheet was a little unorganized. Do you have a paypal account? I'd like to send over a few dollars for my appreciation. I've been working on this all day and you solved it in no time!

no i dont - even if i did, i couldnt accept it.. but thanks :D

 

[TRPlace]

Well here is another challenge. I have 12 worksheets, one for each month. I am trying to get ranges from Sunday to Saturday, but in doing that, some of the ranges overlap into the next month, next worksheet. Is there a way to make this a continual counter...I tried something like this:

=SUMPRODUCT(--('JAN 08'!$A$1:$A$65535>=DATE(2008,1,27)), --('JAN 08'!$A$1:$A$65535<=DATE(2008,2,2)),--('FEB 08'!$A$1:$A$65535<=DATE(2008,2,2)), --('JAN 08'!$F$1:$F$65535="Austin"),--('FEB 08'!$F$1:$F$65535="Austin"))

But that didn't seem to work.