Auto Divide Cell Value to Specified Cells in a Column

Occasional Contributor

Hi. Newbie here, hope you can help me. Im working on a spreadsheet and I have to autodivide a certain number of cases to the number of headcount we have in a day across a column in a range of cells and at the same time if an employee had a remark on the remarks column that they be automatically skipped. Im not sure if I relayed it clearly but I dropped a pic of how I wanted it to be like. Thank you so much in advance! 



9 Replies


In E5:


Fill down to E9.

@Hans Vogelaar 


This is so helpful! How about if I have other values other than "Absent"? How can I add them? I appreciate you so much! 


Let's say you also want to exclude Meeting.

Change COUNTIF($D$5:$D$9,"Absent") to (COUNTIF($D$5:$D$9,"Absent")+COUNTIF($D$5:$D$9,"Meeting"))

and COUNTIF($D$5:$D$9,"<>Absent") to


You can add other options the same way.

You're amazing, Hans! Thank you so much!

@Hans Vogelaar 

Hello, Hans. I got another newbie roadblock. I'd like to set up a workbook in such a way that when I select a month from a dropdown in cell x, the dates along with the values I keyed in every day of those months will autopopulate accordingly. How can I go about doing that? Thank you so much in advance. 



In B3:


In C3:


Fill to the right to AF3.

Apply the custom number format mmm-dd to B3:AF3.


@Hans Vogelaar 

Thank you, Hans. What if I have a data list of all months of the year in A2, and I want to see the records I have in August so I select August right. But I also would like to view September records automatically when I select the September from A2 dropdown. Im not sure if that's a macro. But how can I make it function like that? Im not sure if Im being clear. Haha. I wish I am. Thank you again!


My previous reply will automatically fill the days of the month you enter in A2. So if you enter August, it will enter Aug-01 to Aug-31. If you enter September, it will enter Sep-01 to Sep-30.

It doesn't fill any "records" since I don't know how that would work.

Oh, I see. Thank you so much, again and again, Hans.