IS THERE A FORMULA FOR THIS PROBLEM ?

[Guest]

I am trying to summarise data for a labour organogram for the first time. The data briefly comprises of a list of staff names, the category of staff (fixed, indirect, or direct) and a couple of columns in which to enter the number of days of sick in one column and the number of days off on holiday in the other
I have to summarise this data by staff category. Adding the total number of holiday days by staff category is easy enough by using SUMIF formulas. I also have to count the number of staff by category who are on holiday and thought I could use the COUNTIF formula. I want it to add up the number of cell entries in the holiday column but refer to the staff category column to display the result by staff category

E.G
STAFF NAME CATEGORY DAYS HOLIDAY DAYS SIC
MICHAEL KENT FIXED 5
A.N.OTHER INDIRECT 1
JACK FROST DIRECT 0
JAMES BOND DIRECT 2
TONY BLAIR FIXED 5
HAROLD WILSON FIXED 4

So the formula that I need would look at the category column for say 'fixed' staff and would then return 3 as the result being the number of 'fixed' staff who had holidays by refering also to the days holiday column

Any ideas would be greatly appreciated. THANK

 

[Frank Kabel]

Hi
you may have a look at pivot tables for this. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
for some starting information

 

[Dave R.]

You can use the SUMPRODUCT for this;

=SUMPRODUCT((B2:B10="Fixed")*(C2:C10>0))

C column containing your Days Holiday data, B column containing category.

I am trying to summarise data for a labour organogram for the first time.

The data briefly comprises of a list of staff names, the category of staff
(fixed, indirect, or direct) and a couple of columns in which to enter the
number of days of sick in one column and the number of days off on holiday
in the other.

I have to summarise this data by staff category. Adding the total number

of holiday days by staff category is easy enough by using SUMIF formulas. I
also have to count the number of staff by category who are on holiday and
thought I could use the COUNTIF formula. I want it to add up the number of
cell entries in the holiday column but refer to the staff category column to
display the result by staff category.

E.G.
STAFF NAME CATEGORY DAYS HOLIDAY DAYS SICK
MICHAEL KENT FIXED 5 0
A.N.OTHER INDIRECT 1 0
JACK FROST DIRECT 0 0
JAMES BOND DIRECT 2 0
TONY BLAIR FIXED 5 0
HAROLD WILSON FIXED 4 0

So the formula that I need would look at the category column for say

'fixed' staff and would then return 3 as the result being the number of
'fixed' staff who had holidays by refering also to the days holiday column.

 

[Guest]

Thanks very much for your response Dave - I will give it a try !!!