Countif using dates

[alistair01]

I am trying to count the number of times MO appears in column j in th
last 30 days. where column b is the date of format mm/dd/yyyy hh:mm
Any ideas? I know it will prob be an array formula and i think it migh
be the format of the date that is wrong.
I have tried this and just returns 0 which is not the case.
0
{=SUM((atl!$H$2:$H$4509=4)*(atl!$I$2:$I$4509=13)*(atl!$B$2:$B$4509="?/?2004"))}
Any ideas would be greatly appreciated

 

[Jason Morin]

Try:

=SUM((J2:J4509="MO")*(TODAY()-30<=B2:B4509))

Array-entered.

HTH
Jason
Atlanta, GA

 

[alistair01]

Sorry, Still returns a 0. dont know if it makes a difference but MO i
within a text string

 

[Peo Sjoblom]

Try this adaptation

=SUM(ISNUMBER(FIND("MO",J2:J45))*(TODAY()-30<=B2:B45))

 

[alistair01]

With the last formula its returning a one but the condition occurs abou
50-60 times in the last 30 days. The only other thing i can think of i
that the data is in another sheet but ive added the sheet name befor
the cell numbers the same way i always do e.g.
=SUM(ISNUMBER(FIND("MO",atl!J2:J4509))*(TODAY()-30<=atl!J2:J4509))
Where atl is the sheet name.
Just cant figure this out at all. Hel

 

[Roger Govier]

Hi Alistair

Are you entering this as an array formula? If not, you would probably get an
answer of 1.
Instead of just pressing Enter after the formula, hold down Control + Shift
as you press enter, and Excel will insert a pair of curly braces { }
around your formula.
{=SUM(ISNUMBER(FIND("MO",atl!J2:J4509))*(TODAY()-30<=atl!J2:J4509))}

Do not type the curly braces yourself.

 

[alistair01]

dohh.! cheers for that had been loking at it for too long! was right in
front of my nose!