Countif using dates

  • Thread starter Thread starter alistair01
  • Start date Start date
A

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
 
Try:

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

Array-entered.

HTH
Jason
Atlanta, GA
 
Sorry, Still returns a 0. dont know if it makes a difference but MO i
within a text string
 
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
 
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.
 
dohh.! cheers for that had been loking at it for too long! was right in
front of my nose!
 
Back
Top