CountIF formulas

  • Thread starter Thread starter Cryptographic_ICE
  • Start date Start date
C

Cryptographic_ICE

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.
 
=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)
 
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?
 
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?
Click to expand...

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
 
Stupid me I Forgot the Crt + Shift + Enter. The formula works
perfectly. Now I have slightly different challenge. Same excel sheet
but this time im trying to count categories.

Column E contains the category formatted as text.
Column H contains the date formatted 1/2/2006 8:13:29 AM
Column J contains the amount of time spent on the challenge in the
formatted as "general"

I would like a formula that says

If category = "Networking" AND date=09/20/2006 then add minutes
(column J)

Any help is appreciated.
 
Back
Top