Counter formula for work rota

[Bazil2018]

Im trying to set up a rota in excel but need some help. I need a formula that will check if a cell contains letter E or L (early shift or late). Then at the bottom of table will add them up and display so i know how many people on each shift. Table will be peoples names down column A, then going across will be monday to sunday. Also need to be able to change peoples shifts and see new totals. Sorry if sounds hazy, please message for more details if can help.

 

[iliace]

Hi Bazil,

Can you please post an abbreviated example of what your initial data looks like (with the E and L designations), as well as what you want the output table to look like?

For example, where do you get the people's names, to place in column A?

-Ilia

 

[Bazil2018]

If you can email me then i can reply with template of the design. The rota is for carehome. Staff names are listed on left so remain the same but rota changes weekly or if some one rings in sick then the shift patten may change. Have a template saved but unsure how to send here. People work a mixture of early shifts and late shifts.

 

[iliace]

Maybe that won't be necessary. I would rather everything be posted here, so that others can benefit if they have a similar scenario.

Does your table look something like this?

Name     Mon    Tue    Wed    Thu    Fri    Sat    Sun
Joe L.    E      E      L      L             E
Jane P.   L      L      E      E      L             E
John R.          L      L      E      E      L      L
Mike D.   E                           L      L      E

 

[Bazil2018]

I understand. Yes looks just about same. Need two cells at bottom of each column, one to tell total for earlys and one for lates and if i change an E to an L i need it to alter the totals accordingly.

 

[iliace]

So can you just do something like this - assuming the data is in A1 (i.e. "Name" column heading):

B7 =COUNTIFS(B$2:B$6, "E")
B8 =COUNTIFS(B$2:B$6, "L")

...and copy to the right for columns C:H? Adjust based on the size of your data accordingly.