CountIF formulas
 

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.

CountIF formulas
 

=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.

CountIF formulas
 

Have you thought about using a pivotTable to get your counts

--
regards,
Tom Ogilvy


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.

CountIF formulas
 

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?



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.

CountIF formulas
 

CountIF formulas
 

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.



Alan Beban wrote:
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.