Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formulas | Excel Worksheet Functions | |||
COUNTIF and similar formulas | Excel Worksheet Functions | |||
Formulas - Countif and Fill Down | Excel Discussion (Misc queries) | |||
Countif formulas | Excel Discussion (Misc queries) | |||
COUNTIF FORMULAS | Excel Discussion (Misc queries) |