Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding and summing data in somewhat complex matrix. | Excel Worksheet Functions | |||
Didnt work- Complex Criteria Summing ;( | Excel Discussion (Misc queries) | |||
complex criteria summing | Excel Discussion (Misc queries) | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Complex LookUp / Match Problem ?? | Excel Worksheet Functions |