Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

SUM TOTALS

I would like to sum then totals in the employee totals  for the week range and display them next to the employee names for the week in question.No duplicates in the totals columns.I have attached a file.Thanks
Book1.xls
0
Svgmassive
Asked:
Svgmassive
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Shanan212Commented:
See attached.

There is one condition though. The column D has to be filled in and the formula on column C5 has to be dragged down
Book1.xls
0
 
NBVCCommented:
In A5 add a helper that puts date in each cell

=LOOKUP(10^10,B$5:B5)

copied down

then use Totals formula:

=IF(OR(COUNTIFS(A$5:A5,A5,F$5:F5,F5)>1,F5=""),"",SUMIFS($J$5:$J$18,$A$5:$A$18,A5,$F$5:$F$18,F5))

copied down
0
 
FaustulusCommented:
In order to do what you want using worksheet functions you would need the mother of a whale of a worksheet function or make substantial amends to your worksheet design, some of which can be mitigated but not avoided. Therefore I suggest a coded solution. Note that there are two parcels of code in the attached workbook. You can simply drag the code module TotalsMan into your project in the Project Explorer window of the VB Editor. The event procedure in the code module of Sheet 1 is best transferred by copy / Paste. It must go into the code module of the sheet in your project where you want the action.
At the top of the normal code module (TotalsMan) you have a kind of control panel which looks like this:-
    Enum Nws                        ' Worksheet navigation
        NwsFirstDataRow = 5
        NwsWeek = 3                 ' columns: 3 = C
        NwsID = 7
        NwsTotal = 9
        NwsSummary = 10
    End Enum

Open in new window

The variable names should be self-explanatory, but do ask if you need help. 3, 7, 9 and 10 are column numbers for C, G, I and J respectively. You can modify these numbers if you make modifications to your worksheet.

This code will write a total for each employee's totals in column J next to that employee's ID where it last occurs in that particular week's list. A new total will be drawn at the change of any of the Totals in column I. If a new row is added at the bottom the previous total will be erased and a new total inserted in the new row.

Modifications are simple. You may be tempted to do them yourself. However, if I didn't quite grasp your intentions in some detail do point out the discrepancy and we will get it corrected in no time.
130201-EmployeeTotals.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SvgmassiveAuthor Commented:
Faustulus  that is a very interesting approach  i moved the columns around i am using a formula  then convert it to a value.just got to figure out how to remove the highlighted items with out using a loop.Depending on the number of entries that can slow down performance.i have attached a file.i am looking for the best approach.
130201-EmployeeTotals2.xls
0
 
FaustulusCommented:
As I said, if you want to do the job using a formula it will have to be a big one. There are two difficulties. Firstly, you need to identify the range to be summed up. That can be done by having the week number in every row instead of only once at the beginning of the week. If you don't want to do that the formula will be a monster. Secondly, you want not every line to show the total. The formula will become really voluminous because you need to specify the range several times over, such as for summing and for determining whether to show the total or not, more than once for each of these actions. To construct such a formula would take me several hours - much longer than it took to write the code - and, yes, frankly speaking I don't want to do it because I know you will opt for the code once you see it. You may feel now that having a worksheet function is more familiar than having code. Believe me, the function you are wishing for has nothing familiar about it. It would take an expert a long time to figure out what it does and much longer to find out how it does it.
So, we can discuss the code I have submitted. It doesn't produce the lines you don't want. I notice that you seem to want the employee total to be shown against the first occurrence of the employee's name. I thought it more logical to show the total against the last occurrence. I shall be happy to make that change for you if you have decided to go with the coded approach.
0
 
SvgmassiveAuthor Commented:
point taken.I would like it to be shown next to the first occurrence of the employee if you can make the modification that would be great.
0
 
FaustulusCommented:
0
 
NBVCCommented:
Can I ask why my suggestion wouldn't work?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now