Excel Formula to Sum Based on Employee ID 1

[kwtx04rm]

Need help getting running totals in column E based on EmployeeID and Relation (E= Employee, S= Spouse, C= Children).

Sample data definition:
Column A = EmployeeID is ID of Employee
Column B = InsuredID is ID of Insured (Employee, Spouse, Children)
Column C = Relation is relationship of insured to employee (E= Employee, S= Spouse, C= Children)
Column D = Premium is amount of premium
Column E = Total is sub totals for each: employee, spouse, children

Column A Column B Column C Column D Column E
EmployeeID InsuredID Relation Premium Total
123456789 123456789 E 20.50
123456789 123456789 E 28.00 48.50
123456789 239874561 S 34.70
123456789 239874561 S 30.00 64.70
123456789 323456781 C 6.50
123456789 323456782 C 6.50
123456789 323456783 C 7.00
123456789 323456784 C 7.00 27.00
987654320 987654321 C 7.75
987654320 987654322 C 6.50 14.25

For example, EmployeeID = 123456789 has two 'E' premiums ($20.50 and $28.00), two 'S' premiums ($34.70 and $30.00) and four 'C' premiums ($6.50, $6.50,$7.00, $7.00).

Also, in cases where there are children but no employee, as in EmployeeID= 987654320, still do sub total and flag as exception, possibly format as color red.

I came across some examples using VLOOKUP or SUMPRODUCT but was unable to apply them to this scenario.

Any help is greatly appreciated!

 

[SkipVought]

Hi,

First, I'm using named ranges, based on your headings.
[tt]
=IF(NOT(AND(A2=A3,B2=B3,C2=C3)),SUMPRODUCT((EmployeeID=A2)*(InsuredID=B2)*(Relation=C2)*(Premium)),"")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kwtx04rm]

SkipVought you're fantastic!

And thank you very much for quick response.

Your formula calculated running totals OK for 'E' and 'S' Relations but for some reason it is not doing for 'C'. The EmployeeID = 123456789 has four 'C' premiums ($6.50, $6.50,$7.00, $7.00) that are not totaling up to $27.00, but instead are displaying as individual amounts.

I will try to figure this out...but if you have any suggestions please pass them along.

Again, many thanks!

Regards
kwtx04rm

 

[Gavona]

In case you didn't figure yet, the InsuredID differs for those four cases. If you want the formula to ignore differences in InsuredID then alter the And(...) bit of the formula.

Gavin

 

[SkipVought]

Then just remove the InsuredID criteria.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kwtx04rm]

Skip and Gavona...your help was most valuable.

Thanks!

 

[kwtx04rm]

Greetings,

I am trying to do the same as my earlier post but use Excel VBA instead of using the SUMPRODUCT formula:

=IF(NOT(AND(A3=A4,C3=C4)),SUMPRODUCT((EmployeeID=A3)*(Relation=C3)*(Premium)),"")

I was able to modify Dave Pearson’s code to do most of what I need ('

http://www.pcreview.co.uk/forums/thread-947459.php).

The modified VBA produces correct subtotals grouped by EmployeeID - which is OK.

However, I need subtotals for each Relation (E= Employee, S= Spouse, C= Children) grouped by EmployeeID. Your assistance with this will be of great help!

This is the modified VBA code I am using:

Option Explicit
Sub SubTotalColumn()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim botCell As Range
Dim topCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2
.Rows(FirstRow).Insert
.Cells(FirstRow, "A").Value = "dummyVal"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set topCell = .Cells(LastRow, "A")
Set botCell = .Cells(LastRow, "A")
For iRow = LastRow To FirstRow + 1 Step -1

If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
Set topCell = .Cells(iRow - 1, "A")
Else

If topCell.Address = botCell.Address Then
topCell.Offset(0, 3).Value = topCell.Offset(0, 2).Value
Else

botCell.Offset(0, 3).Formula _
= "=subtotal(9," & topCell.Offset(0, 2).Address(0, 0) _
& ":" & botCell.Offset(0, 2).Address(0, 0) & ")"

End If
Set botCell = .Cells(iRow - 1, "A")
Set topCell = .Cells(iRow - 1, "A")
End If
Next iRow

.Rows(FirstRow).Delete

End With

End Sub

=======================================================

Sample data below:

EmployeeID Relation Premium
123456789 E 9.71
123456789 E 8.60
123456789 E 11.31
987654320 D 6.50
987654320 D 6.50
987654320 E 11.50
987654320 S 12.00
654317157 E 16.50
654317157 E 13.00
654317157 S 14.00
654317157 S 10.25
336658854 D 7.75
336658854 D 6.50
222229375 E 20.50
222229375 E 21.50
222229375 S 26.00
555555959 E 20.50
777776637 E 19.20
777776637 E 24.30
123444119 D 6.50
123444119 D 6.50
123444119 D 7.00
123444119 D 7.00
123444119 E 20.50
123444119 E 28.00
222222280 D 6.50
222222280 D 6.50
222222280 D 6.50
222222280 E 19.10
222222280 E 20.00
222222280 S 30.00
222222280 S 34.70

 

[SkipVought]

One thread per question PLEASE.

In addition, please post VBA (macro code) questions in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Why not use the built-in Data > Subtotal feature (Data > Outline > Subtotal in 2007)?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kwtx04rm]

Thank you Skip, I will post a new thread on the VBA Forum.

Btw, your suggestion to use the built-in Data > Subtotal feature is a good one, but it inserts rows to produce the subtotal at each change of EmployeeID. I only need the subtotal amounts to display one column to the right on change of Relation and EmployeeID. See example of expected results below:

EmployeeID Relation Premium Subtotal
123456789 E 9.71
123456789 E 8.60
123456789 E 11.31 29.62
987654320 D 6.50
987654320 D 6.50 13.00
987654320 E 11.50 11.50
987654320 S 12.00 12.50
654317157 E 16.50
654317157 E 13.00 29.50
654317157 S 14.00
654317157 S 10.25 24.25

 

[SkipVought]

and THAT, can easily be accomplished using an IF statement in that column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

[tt]
=IF(AND(A2=A3,B2=B3),"",SUMPRODUCT((EmployeeID=A2)*(Relation=B2)*(Premium)))
[/tt]
[tt]
EmployeeID Relation Premium Subtotal
123444119 D 6.5
123444119 D 6.5
123444119 D 7
123444119 D 7 27
123444119 E 20.5
123444119 E 28 48.5
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Why use VBA is you can code a simple solution on the sheet!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]