|
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. |
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 05:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com