formula....

  • Thread starter Thread starter Paul Bleijlevens
  • Start date Start date
P

Paul Bleijlevens

I have a workbook with 12 sheets.
Every sheet is 1 month of the year.
On every sheet there are names of peoples that work in our company in column
A.
In column B till AF there are the days 1 .. 31.
If someone has a planned vacation, on the specific day will be a V.
Now a want a total of the V's that every body has planned.
Is there a arrayformula that I can use for this????

PS: The names are not in the same order in the different sheets......
This means on the sheet Januari person 1 could be on row 10 and in
Februari he can be located on row 20 or maybe 40.

Thanks in advance,

Paul Bleijlevens
 
Is there a arrayformula that I can use for this????

Doing this with a formula, array or otherwise, is beyond me. I expect
someone more capable will provide one before long.

You could conceivably use a pivot table with multiple consolidation ranges
.... but it's not ideal.

May I point out that if you had one sheet only with (a) Jan 1st - Dec 31st
in A2 downwards (b) names in B1 across, the whole thing might be a lot more
manageable (input on one sheet only, totals at the bottom, scope for logging
activity other than "V" eg: sick, training, etc.).

Rgds,
Andy
 
Hi Paul

Like the repsonse you have received from Andy, I cannot think of an array
formula to do the whole job in one hit, and I likewise would choose to have
all of my data on a single sheet, even if I then used a different sheet to
summarise that data.

Maybe this is somewhat inelegant, but nevertheless it does provide a
solution.
Create a 13th Sheet as a summary Sheet
In cells B1:M1 type the names of your 12 sheets, Sheet1 Sheet2 or Jan, Feb
whatever you have named them
In cells A2:A100 enter the names of your employees
In cell B2 enter
=SUMPRODUCT((INDIRECT(B$1&"!A1:A100")=$A2)*(INDIRECT(B$1&"!B1:AF100")="V"))
Copy across through C2:M2
Copy down for the range of rows conating employee names

In column N you can sum across for the total days per year for that employee
etc.

Change the ranges to suit the number of employees involved, and whether you
have headings in row 1 of each sheet.
 
While the formula would be fairly long you could use this
assuming your sheets are named
Jan,Feb,Mar etc

on your final sheet in column a list all your employee names
in b1 use

=SUMPRODUCT((JAN!A1:A2000=A1)*(JAN!C1:AK2000="v"))+SUMPRODUCT((FEB!A1:A2000=A1)*(FEB!C1:AK2000="v"))

adding additional sumproducts for each sheet
you will need to adjust the ranges to fit your sheets
and also the sheet names to match yours

then copy this formula down the entire b coumn and you will have a
total count for all the v entries in each sheet

Randall
 
Back
Top