0

I want to calculate (count) the number of times entries appear to the right of someone's name across multiple worksheets (the name will appear on different rows on the different worksheets).

I am calculating shifts on a roster.
I have twelve worksheets - one for each month.
On each worksheet B1 to AF1 are the days of the month.
On each worksheet A2 to A10 are people's names (these change order on different worksheets).
To the right of someone's name (across the month) they have shift names, let's call them S1, S2, S3 and S4.

On worksheet 13 I need to count how many times person one has done each of the different shift names. So all the people's names are listed in alphabetical order down the page, across the top are the different shift names and the cells need to populate the counts from across the other worksheets.

Happy to use VBA if needs be, or formulas if that's even possible.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Matt Allen
  • 11
  • 3
  • Hi and welcome to the forum. Normally, you need to post whatever code you have and indicate where you are stuck. Don't expect people to write code for you from scratch. Furthermore, you don't seem to have put in much research effort into your question, which is probably why somebody downvoted it. – Miqi180 Oct 25 '15 at 10:07
  • Thanks and sorry. I'll be posting what I ended up doing under trincot's answer - as this is close to what I've done. – Matt Allen Oct 25 '15 at 11:11

2 Answers2

1

One idea would be to first do the counting on each sheet:

Add in columns AG to AJ (assuming four shifts) the totals for shifts S1, S2, S3 and S4 respectively, with a formula like this:

=COUNTIF($B1:$AF1; "S1")

Adapt with "S2' in the next column, ... etc.

Then to make things less complex, you would have to ensure that each of the 12 sheets has the same names listed in column A, in the same order. If people join the workforce halfway in March, then also add their names in the sheet for January and February. The same for people who leave: leave their names in all months. You can best type in the names only in the first sheet in column A, and use a formula in all other sheets that picks up this name from the first sheet. Also do this in the 13th sheet.

Then in the 13th sheet, the next columns will be again dedicated to the shifts. Now assuming your other sheet names are like "January" to "December", you can SUM across them as follows (in cell B1):

=SUM(January:December!AG1)
trincot
  • 317,000
  • 35
  • 244
  • 286
  • Thanks for your suggestion. This approach is close to what I ended up coming up with. – Matt Allen Oct 25 '15 at 11:13
  • Thanks. You'll understand that with such exercises one can develop a very flexible solution, but the more flexibility, the more elaborate a solution becomes and long to describe. You did well in developing this further. – trincot Oct 25 '15 at 14:38
0

I've done a similar approach to what Trincot suggested. At the end of each month I added my various shift types and did a count (in this case AG$1 says S1, I've done the same for S2 etc)

   =COUNTIF($B2:$AF2,AG$1)

In my results page (worksheet 13) I've listed everybody's name down the page and used the following formula.

=VLOOKUP($A3,lu_Jan,33,FALSE)+VLOOKUP($A3,lu_Feb,33,FALSE)+VLOOKUP($A3,lu_Mar,33,FALSE)

By doing this I don't have to ensure each name is listed in the same order on every other worksheet.

Matt Allen
  • 11
  • 3