Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 347
Default Complex Summing probably using Match at some point...

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 347
Default Complex Summing probably using Match at some point...

Hmmm...can't find the option to attach the dummy workbook. If someone can
point out my problem there I'll upload it.

"George" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 347
Default Complex Summing probably using Match at some point...

Consider this a dead post now - have written a UDF that will do what I want
so I can call a "simple" formula from the worksheet but still have the
complexity of calculation that I need. Should have done this from the start
:-)

"George" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
finding and summing data in somewhat complex matrix. Willem Excel Worksheet Functions 7 July 10th 07 07:40 AM
Didnt work- Complex Criteria Summing ;( karl41 Excel Discussion (Misc queries) 2 August 15th 06 03:03 AM
complex criteria summing karl41 Excel Discussion (Misc queries) 4 August 14th 06 04:30 PM
Complex Index Match Help (or at least complex to me) Jennifer Reitman Excel Discussion (Misc queries) 3 August 10th 06 08:51 PM
Complex LookUp / Match Problem ?? carl Excel Worksheet Functions 2 May 2nd 05 08:53 PM


All times are GMT +1. The time now is 09:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"