ExcelBanter - View Single Post

Alan Beban

wrote:
I tried the formula you gave but gives a total of 0 and I know there
are some. I tried changing the 2006-09-14 to 200609 but still know
luck. Any Ideas?

Seems to work for me. Perhaps you could supply a small sample of data to
insure that we are working on the same problem.

Alan Beban

Bob Phillips wrote:
=SUM(IF(FREQUENCY(IF(INT(H2:H20)=--"2006-09-14",MATCH(Q2:Q20,Q2:Q20,0)),ROW(
A2:Q20)-ROW(Q2)+1)0,1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
ups.com...
Hello,

I have an excel sheet that contains 2 columns (that are relevant to
this topic)
Column H:H contains a date formatted: 9/14/2006 2:04:27 PM
Column Q:Q contains a job number formatted: 20060914143253. (As you can
see the first 8 characters are also the date). Job numbers are unique
to the job however if more that one person worked on o job there would
be identical job numbers. What I need to do is Count all of the Unique
ticket numbers that occurred on a certain day. I think the statement
would look something like this:

COUNTIF(Job Number is unique AND was created in march)

If anyone could help that would be appreciated.