Hi, I'm hoping someone from this group can help me out with this problem.
It's a temporary solution whilst I write some macros so that the process is
better but I need a "quick-fix" which I'm hoping I'll be able to get by using
some magic combination of formulas.

I have attached a workbook with dummy data in that will paint the picture
better than I can in words although here's a brief description of the
problem. I have a sheet that we use to track what project people are
allocated on. Each day we create a new workbook and each workbook will
contain each project on a separate sheet. There are three groups of employees
on each project (for sub departments).

I'd like to work out a formula that will react to row insertions (and
deletions) that counts up the people who are present and absent for each
group. It would also need to look at those people who are marked as "Exclude"
(see workbook).

The current formula I'm using is:
=COUNTIF(G14:G39,"YES")-SUMPRODUCT((G14:G39="YES")*(M14:M39="Exclude"))
(some cell refs are wrong there...the dummy allocation attached has
different cols)

What I'm after is some way of changing the "G39" reference so that it looks
for the next cell with "PRESENT" in it after a particular cell. In this way
it should react to changes in the document.

I'm thinking that I could use some combination of the SUMPRODUCT I already
use and the MATCH formula (or FIND perhaps?) to correctly determine the right
range for the appropriate group. Of course the last group should be easy as I
can just go from the bottom :-)

As I said I am writing some macros to tidy this application up but until
then does anyone have any suitable suggestions for sorting this out?

Thanks
George