Sum col with reference to criteria in other col

  • Thread starter Thread starter Boss
  • Start date Start date
B

Boss

I have foll data

criteria score Unique answer
yes 4 yes 20
yes 6 no 116
yes 6
yes 4
no 45
no 57
no 6
no 8

I need is sum of all the score for each criteria

Yes would give 20
No would give 116

I tried the foll...

Range("b3").Value = Application.WorksheetFunction.SumIf(Range("a:a"),
Range("c2"), Range("b:b"))

Problem is the criteria "c2", i wish to change using a loop. i cannot use
sumproduct because the criteria are more in number close to 50.

Thsi something very imp for me and i am badly stuck.. please help..

Thanks!
Boss
 
with data in a1:b8

excel formulas
=SUMPRODUCT((A1:A8="yes")*(B1:B8))
=SUMIF(A1:A8,"=yes",B1:B8)


in vb, if you just want the values

range("e3").Value = application.Evaluate("SUMPRODUCT((A1:A8=" & """yes""" &
")*(B1:B8))")
range("f3").Value = application.Evaluate("SUMIF(A1:A8," & """=yes""" &
",B1:B8)")
 
Thanks for the reply but as i said i caanot use sumproduct because i have
many criteria to sum for.
i have database of 250 rows where scores of 70 emp is filled. I have to pull
the total score of each employee in a different sheet.
i need to find the total score of each emp..

Hope thsi makes issue clear.. please help..

Thanks a lot..
Boss
 
give an example of the data so we can help.



Boss said:
Thanks for the reply but as i said i caanot use sumproduct because i have
many criteria to sum for.
i have database of 250 rows where scores of 70 emp is filled. I have to
pull
the total score of each employee in a different sheet.
i need to find the total score of each emp..

Hope thsi makes issue clear.. please help..

Thanks a lot..
Boss
Click to expand...
 
EMP name Score

Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 9
Leo Brown 10
Andrea Jones 10
Paul Adams 10
Andrea Jones 10
Andrea Jones 10
Andrea Jones 10
Andrea Jones 13
Leo Brown 13
Leo Brown 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 13
Andrea Jones 18
Bob Hudson 18
Andrea Jones 20
Andrea Jones 20
Andrea Jones 21
Leo Brown 21
Leo Brown 21
Leo Brown 21
Leo Brown 23
Leo Brown 43
Leo Brown 22
Leo Brown 12


It continues till 70 emp with more than 250 records. I need the total score
of each emp in other sheet.
It is a huge project on which i am working, entire coding is done i am stuck
up in this.. please help..

Thanks!
Boss
 
ok, try this. i assumed that the sheetnames were sheet1 and sheet2. that the
names start in a2 and the scores in b2 on sheet1. there are column headings
on sheet2, name and score. i assumed i could use c1 on sheet1 for a subtotal
formula, move it if you have something in c1 and change the references to
c1.
watch for word-wrap in the post, there are no line breaks in the code


Sub consolidate()
Dim enames As Collection
Dim lastrow As Long
Dim j As Long, i As Long
Dim c As Range
Dim empStr As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim escore As Double
i = 2
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("C1").Formula = "=subtotal(109,b2:b" & lastrow & ")"
Set enames = New Collection
For Each c In ws.Range("A2:A" & lastrow)
On Error Resume Next
empStr = Trim(c.Value)
enames.Add empStr, CStr(empStr)
On Error GoTo 0
Next
For j = 1 To enames.Count
With ws.Range("A1:B" & lastrow)
.AutoFilter Field:=1, Criteria1:=enames(j),
Operator:=xlAnd
End With
Debug.Print enames(j)
escore = ws.Range("C1").Value
ws2.Range("A" & i) = enames(j)
ws2.Range("B" & i) = ws.Range("C1").Value
i = i + 1
Next
ws.AutoFilterMode = False
End Sub
 
TOO GOOD....

You made my day...
Thanks!
Boss

Gary Keramidas said:
ok, try this. i assumed that the sheetnames were sheet1 and sheet2. that the
names start in a2 and the scores in b2 on sheet1. there are column headings
on sheet2, name and score. i assumed i could use c1 on sheet1 for a subtotal
formula, move it if you have something in c1 and change the references to
c1.
watch for word-wrap in the post, there are no line breaks in the code


Sub consolidate()
Dim enames As Collection
Dim lastrow As Long
Dim j As Long, i As Long
Dim c As Range
Dim empStr As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim escore As Double
i = 2
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("C1").Formula = "=subtotal(109,b2:b" & lastrow & ")"
Set enames = New Collection
For Each c In ws.Range("A2:A" & lastrow)
On Error Resume Next
empStr = Trim(c.Value)
enames.Add empStr, CStr(empStr)
On Error GoTo 0
Next
For j = 1 To enames.Count
With ws.Range("A1:B" & lastrow)
.AutoFilter Field:=1, Criteria1:=enames(j),
Operator:=xlAnd
End With
Debug.Print enames(j)
escore = ws.Range("C1").Value
ws2.Range("A" & i) = enames(j)
ws2.Range("B" & i) = ws.Range("C1").Value
i = i + 1
Next
ws.AutoFilterMode = False
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SumIf formula from one column based on criteria from two other col 10
How to use Sumifs with vertically and horizontally criterias 3
Having a problem with calculating medians in access 1
function returning SUM of 2 adjacent cols in 2 separate cells 7
FORMULA TO SUM ON CRITERIA 2
counter in "If" condition 2
cell returning totals of 2 cols+ sending this value to another col 5
Help with code please 4

Back
Top