| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting D,E,N
I got a formula to count shifts worked in daytime=D, evening=E and for night
shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting D,E,N
Try something like this:
To count the D, E, and N shifts in A1:E1: F1: =SUM(COUNTIF($A1:$E1,"*"&{"D","E","N"}&"*")) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... I got a formula to count shifts worked in daytime=D, evening=E and for night shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting D,E,N
G1:H1 = D, E, N
Enter this formula in G2 then copy across to H2 then down as needed: =COUNTIF($A2:$E2,"*"&G$1&"*") -- Biff Microsoft Excel MVP "FC" wrote in message ... I got a formula to count shifts worked in daytime=D, evening=E and for night shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting D,E,N
Yes it is! Thanks a lot Ron and keep the good work, specially on weekends
that everyone is absent from the discussion group. Thanks again. "Ron Coderre" wrote: Try something like this: To count the D, E, and N shifts in A1:E1: F1: =SUM(COUNTIF($A1:$E1,"*"&{"D","E","N"}&"*")) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... I got a formula to count shifts worked in daytime=D, evening=E and for night shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting D,E,N
Typo...
G1:H1 = D, E, N Should be: G1:I1 = D, E, N -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G1:H1 = D, E, N Enter this formula in G2 then copy across to H2 then down as needed: =COUNTIF($A2:$E2,"*"&G$1&"*") -- Biff Microsoft Excel MVP "FC" wrote in message ... I got a formula to count shifts worked in daytime=D, evening=E and for night shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting D,E,N
I'm so glad I could help......Thanks for the feedback.
-------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... Yes it is! Thanks a lot Ron and keep the good work, specially on weekends that everyone is absent from the discussion group. Thanks again. "Ron Coderre" wrote: Try something like this: To count the D, E, and N shifts in A1:E1: F1: =SUM(COUNTIF($A1:$E1,"*"&{"D","E","N"}&"*")) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... I got a formula to count shifts worked in daytime=D, evening=E and for night shift=N.( D,E,N are in each cell per day of the week per worker) My formula is : =SUM(COUNTIF(A1:E1,{"D""E""N"})). Now I have workers doing double shift, that is either DE, EN or ND. How can I adjust this formula or use another one ( welcome all sugestions) in order to count D,E,N as 1 item and DE,EN,ND as 2 items or 2 shifts ? In excel languaje : A B C D E D E N 1 D E D N N 2 1 2 2 D DE DE DE EN 4 4 1 Don't need, don't want macros. Thanks a lot in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting | New Users to Excel | |||
| counting | Excel Discussion (Misc queries) | |||
| Counting help | Excel Discussion (Misc queries) | |||
| Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
Linear Mode
