Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Summing (2000)

    I have a list of data and want to add up the number of people working on specific projects. Here are the row headers: inv_id status charge_date Charge Month Firm_Name Matter_Name Professional mattertype casecategory chargedesc expensedesc taskdesc chargetype units rate total_amount inv_adjustment tklevel fullname Test

    When Firm Name(column E) does not change in subsequent rows, and Matter Name (Column F) doesn't change, I'd like to count the number of unique Fullnames (columnS) across the spreadsheet.

    Any ideas?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Summing (2000)

    Try using Data - Subtotals and excel will add subtotals at the changes

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    This doesn't get me what I need. I need to count the number of unique personnel working if column E and F don't change. Note: Person A may appear in the list, with E and F constant, a number of times. Any other ideas?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Summing (2000)

    Have you tried the D-Count function?

    This calculates based on a criteria (the same type of criteria you would use with adv filter).

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    No. I have never heard of it. How would I apply to this situation?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Summing (2000)

    Do you have Microsoft Access? This seems to be more suitable for a database than for a spreadsheet.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Summing (2000)

    You could use Data | Filter | Advanced Filter to copy unique records to another location, then create a pivot table based on the extracted unique records. The downside is that you'd have to redo this when the data change.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Summing (2000)

    Look in the help under "Database functions" Excel has some examples.

    It is much like using "adv filter": Your data must be set up in a "database" and you must have a "criteria" range setup also. Your original question is not detailed enough to give a working example (or even know for sure how appropriate the technique is)

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    Sorry my original question was not detailed enough. What I am/was trying to do is the following:'
    1. Have a list of people working on various projects. They enter time as they work. Billing is monthly. Consequently, person A may have 20 time entries for the month and person B only have one. In this case there is only 2 time keepers working on this project.
    2. I want to do the following: When the firm submitting the time does not change from row 1 to subsequent rows, and the project name does not change between row 1 and subsequent rows, I want to subtotal the number of unique timekeepers working on the project. Note the spreadsheet may have 100 different firms submitting time using 100 different projects, each with multiple timekeepers. The end goal of this is to have a subtotal of unique timekeepers working on each project and a grand total of unique timekeepers working on all projects.

    I hope this is a better explanation.

    Thanks for the help.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    Hans, hope you are well. I have done something similar to this; however, when I do this I must delete part of the columns to make it work. For example, person A may work on project A, and have several different time entries - each being unique. When I do this, unless I delete the time entry column, each row may appear to be unique. Then when I try to count the number of timekeepers, person A will show up on project A many different times making my end result wrong. Anyway, I got something to work using the Advanced Filter but it was a multi-step process and not conducive to repeating each time I want to do this feat since it was very time-consuming.

    Thanks for your help and if you have any other thoughts, please share.

    Have a great day.

    Mitch

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Summing (2000)

    You could use a macro to repeat the process. I have attached a simple demo workbook with a macro that re-creates the list of unique entries and the pivot table. Of course, it will have to be adapted for your situation.

  12. #12
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    Hans, thanks. This does not count unique items does it? Looks like to me it counts the number of occurrences of a each individual item. Am I incorrect? I have attached a sample of what I want to do. Maybe that will help. In my sample, I'd like to be able to subtotal the number of unique timekeepers per case.

    Anyway, thanks again!

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Summing (2000)

    Could you explain (based on this example) what "output" you would like? It seems like a pivot table to give what you want, but perhaps I am missing something.

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    I'd like a output either in subtotal type format, or pivot table format. Have been unable to accomplish with a pivot table - counting unique occurences within a large range of data similar to what I attached.

  15. #15
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing (2000)

    Hans, I now see what this is doing and think it works. I am not much at VB (among other things). Is it supposed to create a pivot table for me as well, automatically? It does not, but looks like it should by the code...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •