I work on schedule, would like to calculate how many are morning shift and how many are afternoon shift base on market.
I've tried if with countifs, arrayformula with countifs, it doesn't seem to work. Now I've totally out of idea.
Below is an example of what I trying to do. I want to get the number (highlighted in yellow) by using formula, cause the headcount is too many, I couldn't do it with manual counting.
Best Answer
I'll take a crack at this, I believe I understand what you are trying to accomplish.
I think nesting all of this in one (or two formulas) for AM and PM would be a bit of a complicated formula. So I think the easiest way to help you approach this is to break it up into simpler chunks.
Make a summary column for AM and PM after your Sunday column that counts how many AM or PM are in each row. ex. =Countif(C2:I,"=AM"). You would need to repeat this for each row since Arrayformula() doesn't play nice with Countif. In my sheet below I use a different formula to make this iterate with each new row automatically. See that sheet below.
Now you can more simply aggregate each 'market' and sum the values in either of the AM or PM total fields. The formula would be something like this =sumif(B2:B,"="&M2:M,J2:J) Where B is your market column, M is the Market in your Yellow Table, and J is the column that sums up AM. This can more simply be put in an array formula to automatically populate as Markets are added e.g =array_constrain(ArrayFormula(Sumif(B2:B,"="&M2:M,J2:J)),CountA(M2:M),1)
If you wish, you might be able to combine all of these concepts into a single formula, but I'll leave that to you as I think this will get you where you need to be and is less complicated.
Here is a sheet with the solution for you to make a copy if you wish to explore more. I hope this helps!
https://docs.google.com/spreadsheets/d/1rz29ufOPKhdAeX-Fo3XIV_ipZ6OZQQt5ERYYZQ98-sk/edit?usp=sharing