R
raehippychick
Each week I have to find the total number of employees by grade - fo
example there could be five employees in grade Z100 this week and 7 th
next week.
However the way the spreadsheet is designed is that the grades are no
all together - they may be in different rows and in different column
as columns A,B&C are replicated across a few times
Sheet is basically like this:
Column A has Employee Grade e.g. Z100
Column B has COUNTA of the grades [=COUNTA(C3:C12)]
Column C has employee names
Grades may be in row 3 but will have a number of rows next to and belo
with names in column C, but Directly next to the Grade cell there is
COUNTA - I would like to total all COUNTA cells but conditionally so
can have a total for each grade
A B C
Z100 2 Fred
Harry
Z200 3 Mary
Andy
Ann
Z100 1 Bert
I cannot alter this spreadsheet as it is not mine but I could reques
the totals I need to be added at the bottom
Please could anyone help me with a formula that will look at the whol
sheet area and find and total of each instance e.g. find all people wh
are Z100 grade?
So that in Column B at the bottom of the rest of the data I could hav
a list of totals by grade?
I have used the following suggestion =SUMIF(A1:A100,"Z100",B1:B100) bu
this gives me just the ability to sum up the column - I could hav
grade 100 in two or three rows, so I have copied this formula dow
under each column (substituting the grade code each time) then used
transposed version of this formula at the end of each of these ne
rows
Ideally I would like to look at the whole area of data to pull out th
total rather than each column individually
I gather that using LOOKUP means the data has to be sorte
alphabetically and this is not possible in this spreadsheet
Is this possible?
Many thank
example there could be five employees in grade Z100 this week and 7 th
next week.
However the way the spreadsheet is designed is that the grades are no
all together - they may be in different rows and in different column
as columns A,B&C are replicated across a few times
Sheet is basically like this:
Column A has Employee Grade e.g. Z100
Column B has COUNTA of the grades [=COUNTA(C3:C12)]
Column C has employee names
Grades may be in row 3 but will have a number of rows next to and belo
with names in column C, but Directly next to the Grade cell there is
COUNTA - I would like to total all COUNTA cells but conditionally so
can have a total for each grade
A B C
Z100 2 Fred
Harry
Z200 3 Mary
Andy
Ann
Z100 1 Bert
I cannot alter this spreadsheet as it is not mine but I could reques
the totals I need to be added at the bottom
Please could anyone help me with a formula that will look at the whol
sheet area and find and total of each instance e.g. find all people wh
are Z100 grade?
So that in Column B at the bottom of the rest of the data I could hav
a list of totals by grade?
I have used the following suggestion =SUMIF(A1:A100,"Z100",B1:B100) bu
this gives me just the ability to sum up the column - I could hav
grade 100 in two or three rows, so I have copied this formula dow
under each column (substituting the grade code each time) then used
transposed version of this formula at the end of each of these ne
rows
Ideally I would like to look at the whole area of data to pull out th
total rather than each column individually
I gather that using LOOKUP means the data has to be sorte
alphabetically and this is not possible in this spreadsheet
Is this possible?
Many thank