Conditional sum question

[Isis]

I am trying to sum up wages for shifts worked on a worksheet.

My worksheet rows have a date - name - shift1 - name - shift2 - name -
shift3

On the far right of each of the above rows (but in the same row) are;

Hours1 - Rate1 - Hours2 - Rate2 - Hours3 - Rate3

I have the 'name' cell in each row set to use a drop down with the
possible names

the Hours1, Rate1 etc cells are meant to provide a means of calculating
the total due to the 'name' that is set for each shift.

I then want to list the names of all those appearing in the rows in a
list at teh bottom of the sheet - and I want to total all the Hours*Rate
that are due to each carre worker.

I don't mind using VBA or just a function, but I can't find a way of
doing so many calculations in one go for the totals.

Some pointers, info or help would be great !

Thanks

 

[joel]

Use Advance filter with the unique option to get a unique list of names
You can then use sumproduct to get the totals for each name. Advanc
Filter you can either do manually form the Data Menu or from VBA. I
you use VBA then put a formula next to each person name containing th
Sumproduct formula.

This is the VBA code

Sub GetUniqueNames()

'put names into column IV
'then use advancefilter to put names at bottom
'of worksheet

'use data in column A to get Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'put final list 5 rows down from last date
NewRow = LastRow + 5

'copy first set of names in column B to column IV
Range("B2:B" & LastRow).Copy _
Destination:=Range("IV1")
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy Second List of names in column D to column IV
Range("D2" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy third List of names in column F to column IV
Range("F2:F" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'use Advance filter to move copy data
Range("IV1:IV" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("A10"), _
Unique:=True

'delete temporary data in column IV
Columns("IV").Delete

LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
'Unique names goes from NewRow to LastRowUnique


'assume hours 1 rates 1 in column JK
'assume hours 2 rates 2 in column LM
'assume hours 3 rates 3 in column NO
'sample of the formula below
'=SUMPRODUCT(--(B$2:B$5=A10)*J$2:J$5*K$2:K$5)+
' SUMPRODUCT(--(B$2:B$5=A10)*L$2:L$5*M$2:M$5)+
' SUMPRODUCT(--(B$2:B$5=A10)*N$2:N$5*O$2:O$5)


'put formula in first row of unique names in column B
Range("B" & NewRow).Formula = _
"=SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*J$2:J$" & LastRow & "*K$2:K$" & LastRow & ")+" & _
"SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*L$2:L$" & LastRow & "*M$2:M$" & LastRow & ")+" & _
"SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*N$2:N$" & LastRow & "*O$2:O$" & LastRow & ")"

'copy formula down column B for each unique name
Range("B" & NewRow).Copy _
Destination:=Range("B" & NewRow & ":B" & LastRowUnique)

End Sub

 

[Isis]

Use Advance filter with the unique option to get a unique list of names.
You can then use sumproduct to get the totals for each name. Advance
Filter you can either do manually form the Data Menu or from VBA. If
you use VBA then put a formula next to each person name containing the
Sumproduct formula.

This is the VBA code

Sub GetUniqueNames()

'put names into column IV
'then use advancefilter to put names at bottom
'of worksheet

'use data in column A to get Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'put final list 5 rows down from last date
NewRow = LastRow + 5

'copy first set of names in column B to column IV
Range("B2:B" & LastRow).Copy _
Destination:=Range("IV1")
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy Second List of names in column D to column IV
Range("D2" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy third List of names in column F to column IV
Range("F2:F" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'use Advance filter to move copy data
Range("IV1:IV" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("A10"), _
Unique:=True

'delete temporary data in column IV
Columns("IV").Delete

LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
'Unique names goes from NewRow to LastRowUnique


'assume hours 1 rates 1 in column JK
'assume hours 2 rates 2 in column LM
'assume hours 3 rates 3 in column NO
'sample of the formula below
'=SUMPRODUCT(--(B$2:B$5=A10)*J$2:J$5*K$2:K$5)+
' SUMPRODUCT(--(B$2:B$5=A10)*L$2:L$5*M$2:M$5)+
' SUMPRODUCT(--(B$2:B$5=A10)*N$2:N$5*O$2:O$5)


'put formula in first row of unique names in column B
Range("B" & NewRow).Formula = _
"=SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*J$2:J$" & LastRow & "*K$2:K$" & LastRow & ")+" & _
"SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*L$2:L$" & LastRow & "*M$2:M$" & LastRow & ")+" & _
"SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & NewRow & _
")*N$2:N$" & LastRow & "*O$2:O$" & LastRow & ")"

'copy formula down column B for each unique name
Range("B" & NewRow).Copy _
Destination:=Range("B" & NewRow & ":B" & LastRowUnique)

End Sub

Joel - that is a 'serious' reply, containing a lot of work - I am much
obliged.

I am going through the code as the question I asked was simplified so I
need to apply it now to my case - it's great that you commented the code
so well, thank you very much.

I am getting a duplicate name for some reason - am I allowed to upload
the sheet to the group do you know ?

Thanks again

Regards,

Tobias

 

[joel]

I posted the sode at THECODECAGE. After my first posting I realized th
duplicate entry problem and edited the code. You may not of gotten th
changes I made so her is the code with the changes

the duplicate entry is a bug with AdvanceFilter method. Advance filte
requires a header row, otherwise the 1st entry is duplicated. th
change I made adds a header row to column IV.



Sub GetUniqueNames()

'put names into column IV
'then use advancefilter to put names at bottom
'of worksheet

'use data in column A to get Last Row
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'put final list 5 rows down from last date
NewRow = LastRow + 5

'put header in IV1 so advance filter doesn't create duplicate entry
Range("IV1") = "Unique Names"
'copy first set of names in column B to column IV
Range("B2:B" & LastRow).Copy _
Destination:=Range("IV2")
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy Second List of names in column D to column IV
Range("D2" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'Copy third List of names in column F to column IV
Range("F2:F" & LastRow).Copy _
Destination:=Range("IV" & (LastRowNewData + 1))
'get last row of new data
LastRowNewData = Range("IV" & Rows.Count).End(xlUp).Row
'use Advance filter to move copy data
Range("IV1:IV" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("A10"), _
Unique:=True

'delete temporary data in column IV
Columns("IV").Delete

LastRowUnique = Range("A" & Rows.Count).End(xlUp).Row
'Unique names goes from NewRow to LastRowUnique


'assume hours 1 rates 1 in column JK
'assume hours 2 rates 2 in column LM
'assume hours 3 rates 3 in column NO
'=SUMPRODUCT(--(B$2:B$5=A10)*J$2:J$5*K$2:K$5)+
' SUMPRODUCT(--(D$2$5=A10)*L$2:L$5*M$2:M$5)+
' SUMPRODUCT(--(F$2:F$5=A10)*N$2:N$5*O$2:O$5)


'put formula in first row of unique names in column B
Range("B" & (NewRow + 1)).Formula = _
"=SUMPRODUCT(--(B$2:B$" & LastRow & "=A" & (NewRow + 1) & _
")*J$2:J$" & LastRow & "*K$2:K$" & LastRow & ")+" & _
"SUMPRODUCT(--(D$2$" & LastRow & "=A" & (NewRow + 1) & _
")*L$2:L$" & LastRow & "*M$2:M$" & LastRow & ")+" & _
"SUMPRODUCT(--(F$2:F$" & LastRow & "=A" & (NewRow + 1) & _
")*N$2:N$" & LastRow & "*O$2:O$" & LastRow & ")"

'copy formula down column B for each unique name
Range("B" & (NewRow + 1)).Copy _
Destination:=Range("B" & (NewRow + 1) & ":B" & LastRowUnique)

End Sub