IS THERE A FORMULA FOR THIS PROBLEM ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.

MICHAEL KENT said:
I am trying to summarise data for a labour organogram for the first time.
Click to expand...
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
Click to expand...
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
Click to expand...
'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.
 
Back
Top