Summing instances that are in different rows and columns

[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

 

[raehippychick]

If I'm understanding your question, you might want to look at th
COUNTIF Function. Set it to count instances of "Z100". You'll nee
the "" because, by default, Excel will read Z100 as text.
Something like =COUNTIF(Your_Range,"Z100") should work.


tj

tj

Thanks for that - it is something that I will use in anothe
spreadsheet I have, but my problem is that the grade codes are use
like a heading. What it is is that Z100 would be in column A and the
in the cell next to it (B) will be a count of employees from column C
Z100 could also appear in columns D and E too

Col A Col B Col C
A1= Z100 * B1= 2* C1= Fred
C2= Harry
A3= Z200 * B3= 3* C3= Mary
C4= Andy
C5= Ann
A6= Z100 *B6= 1* C6= Bert


I am hoping to be able to sum all the count cells next to all Z10
cells in one place