Conditional sum question
 

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

Conditional sum question
 

joel wrote in
:


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:D" & 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