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. enter image description here

  • Welcome to Web Applications. Show the formulas that you tried. Dec 1, 2020 at 16:20
  • @Isaac, I've seen this post a few times now, and I've moved on each time, because I don't understand how your numbers in yellow match the chart above. I suggest that you explain in detail how you got each 0, 1 or 2 in the AM/PM chart in yellow. Also, we can't tell from your image what the rows or columns are; so even if we understand the problem, we can't write efficient formulas. Help us help you: share a link to a sample sheet.
    – Erik Tyler
    Dec 5, 2020 at 21:39

1 Answer 1


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. enter image description here

  • 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)

enter image description here

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!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.