Help with IF

[systemx]

Hiya!

I'm hoping someone can help me with an IF statement that involves a
condition.

For simplicity sakes, I have a list of names in the 'A' column, time
blocks in 'B' and 'C', then a type of work in 'D'. If the person is
working, there is a '1' in the time block.

eg.

7.30 8.00
Bob Smith 1 1 Licking
Envelopes
Jim Fisher 1
Sealing Envelopes
Sarah Norton 1 Licking Envelopes

I also have a master sheet, which will contain the different 'tasks'
and time blocks, and total how many people will be working on that task
at any given time.

With working formula this would look like -

7.30 8.00
Licking Envelopes 1 2
Sealing Envelopes 0 1

I'm trying to figure out how to get the formulae working for this. For
one row, it is easy enough.

=IF(WORKSHEET!$D2=\"Licking Envelopes\",IF(WORKSHEET!B2=1,1,0))

Then just change the references accordingly for each time slot. I can
also add additional employees by using .....

=IF(Bob Smith is licking envelopes) + IF(Jim Fisher is licking
envelopes) ...and so on.

But with 100+ employees this approach does not make sense. (Probably
neither does my post!)

I'm sure there must be an easier was to count how many of the employees
are licking envelopes in any given timeslot. I've tried playing around
with AND and COUNTIF's to no avail. Have also tried changing the cell
reference in the formula to a range of cells (eg instead of D2 use
'D25') - but of course it still returns 1.

Not quite sure how else to tell excel to count how many people match
the condition, without using my poor example above. Any help would be
appreciated!!!!!

Thanks

Rob

 

[Bob Phillips]

In the first master totals cell enter

=SUMIF(Sheet1!$D:$D,$A2,Sheet1!B:B)

and copy across and down

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[systemx]

Thank you. Worked like a charm.

I had to swap a couple of things about in the formula....but I put that
down to my own post not making sense!

Thanks again.

 

[Bob Phillips]

Thank you. Worked like a charm.

I had to swap a couple of things about in the formula....but I put that
down to my own post not making sense!

Not necessarily )