Conditional Summing (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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

Steve

Reply | Quote

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

Steve

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Here’s a solution without VBA. Your list will have to be sorted by case, and you need to use a summary area on the sheet with a unique list of cases. Unwanted columns can be hidden.

Ken

Reply | Quote

Neat. Can you briefly expound on the formulas for my benefit?

Thanks again.

Reply | Quote

Explanation in attachment.

Happy Thanksgiving!

Reply | Quote

Explanation in attachment.

Happy Thanksgiving!

Reply | Quote

Neat. Can you briefly expound on the formulas for my benefit?

Thanks again.

Reply | Quote

Here’s a solution without VBA. Your list will have to be sorted by case, and you need to use a summary area on the sheet with a unique list of cases. Unwanted columns can be hidden.

Ken

Reply | Quote

A2:B11 houses:

{“Case”,”Emp”;1,”a”;1,”a”;1,”b”;2,”b”;2,”c”;2,”c”;3,”c”;3,”d”;3,”a”}

Note that the empty in-between row is removed.

C2 must house a 0.

In C3 enter & copy down:

=IF((A3″”)*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,””)

In G1 enter:

=LOOKUP(9.99999999999999E+307,C3:C11)

In G2 enter: Distinct Cases

In G3 enter & copy down:

=IF(ROW()-ROW(G$3)+1<=$G$1,INDEX($A$3:$A$11,MATCH(ROW()-ROW(G$3)+1,$C$3:$C$11)),"")

In H2 enter: Subtotal of unique timekeepers per case

H3:

=COUNT(1/FREQUENCY(IF(($A$3:$A$11=G3)*($B$3:$B$11″”),MATCH($B$3:$B$11,$B$3:$B$11,0)),ROW(INDEX(B3:B11,0,0))-ROW(B3)+1))

which must be confirmed with control+shift+enter (instead of just with enter) then copied down.

See the attachment.

Reply | Quote

I certanily appreciate your help. This solution works great. Can you elaborate, briefly, on your formulas?

Thanks.

Reply | Quote

I certanily appreciate your help. This solution works great. Can you elaborate, briefly, on your formulas?

Thanks.

Reply | Quote

A2:B11 houses:

{“Case”,”Emp”;1,”a”;1,”a”;1,”b”;2,”b”;2,”c”;2,”c”;3,”c”;3,”d”;3,”a”}

Note that the empty in-between row is removed.

C2 must house a 0.

In C3 enter & copy down:

=IF((A3″”)*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,””)

In G1 enter:

=LOOKUP(9.99999999999999E+307,C3:C11)

In G2 enter: Distinct Cases

In G3 enter & copy down:

=IF(ROW()-ROW(G$3)+1<=$G$1,INDEX($A$3:$A$11,MATCH(ROW()-ROW(G$3)+1,$C$3:$C$11)),"")

In H2 enter: Subtotal of unique timekeepers per case

H3:

=COUNT(1/FREQUENCY(IF(($A$3:$A$11=G3)*($B$3:$B$11″”),MATCH($B$3:$B$11,$B$3:$B$11,0)),ROW(INDEX(B3:B11,0,0))-ROW(B3)+1))

which must be confirmed with control+shift+enter (instead of just with enter) then copied down.

See the attachment.

Reply | Quote

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!

Reply | Quote

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…

Reply | Quote

You will need to set up a range of field names for the unique items – like F1:H1 in my demo – and you will have to tweak the macro to fit your situation:
– Adjust the various cell adresses
– Adjust the field names.
If you wish, you can post a copy of your workbook (remove sensitive or proprietary information)

Reply | Quote

You will need to set up a range of field names for the unique items – like F1:H1 in my demo – and you will have to tweak the macro to fit your situation:
– Adjust the various cell adresses
– Adjust the field names.
If you wish, you can post a copy of your workbook (remove sensitive or proprietary information)

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote