Add Multiple Values Based on Criteria

[masterton]

Add Multiple Values Based on Criteria

Search for the word "Clear" in the Task column.
If the word "Clear" is found, go to Value column.
Add all values starting from "Clear" down to the end of the list.
If it is larger than 10, return the word "OK". Otherwise "Poor".
If 20, return the word "Good".
The length of the list is variable.

Here is the sample worksheet (Please click).

How should I do?
Thanks a lot.

 

[Roderick_E]

hmm, the word "spy" in the link isn't too inviting

 

[masterton]

hmm, the word "spy" in the link isn't too inviting

Hahaha... Well it is just an online spreadsheet link, auto-created by MS.
I agree. MS likes to spy.

In case if you are worried, here is the full link.

 

[Roderick_E]

blocked domain from my work computer. Sorry can't see your example.

 

[masterton]

blocked domain from my work computer. Sorry can't see your example.

That's bad. Your workplace blocked live.com.

Here is the screenshot.

 

[masterton]

Add Multiple Values Based on Criteria

Search for the word "Clear" in the Task column.
If the word "Clear" is found, go to Value column.
Add all values starting from "Clear" down to the end of the list.
If it is larger than 10, return the word "OK". Otherwise "Poor".
If 20, return the word "Good".
The length of the list is variable.

Here is the sample worksheet (Please click).

How should I do?
Thanks a lot.

Clarification:
Each table is treated separately.
If it is larger than 10, return the word "OK". Otherwise "Poor".
If larger than 20, return the word "Good".
If it couldn't find "Clear", return "No match".

 

[Roderick_E]

Here you go. This will work with the example you showed me and has enough flexibility to be modified a bit by setting the Dims.

Option Compare Text 'ignore text case
'--------------------------------
Function ConvertToLetter(iCol As Integer) As String
'*** convert col number to letter ***
   If iCol <= 26 Then
   'columns A-Z
   ConvertToLetter = Chr(iCol + 64)
   Else
   ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & Chr(((iCol - 1) Mod 26) + 65)
   End If
End Function
'--------------------------------
Sub clearcheck()
Dim ssheet As String
Dim tablecnts As Integer
Dim tablehead As Integer
Dim inittable As Integer
Dim rpt As String
Dim okcrit As Integer
Dim poorcrit As Integer
Dim goodcrit As Integer
Dim result As String
ssheet = "Sheet1" ' actual name of source sheet
tablecnts = 4 'number of tables to process
tablehead = 3 'header row of tables
inittable = 1 'first table's column number
okcrit = 10 'returns OK if value > okcrit
poorcrit = 10 'returns POOR if value < poorcrit
goodcrit = 20 'returns GOOD if value => goodcrit
'***will assume 1 blank column between tables
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For x = 1 To tablecnts
taskrng = ConvertToLetter(Val(inittable)) & "1:" & ConvertToLetter(Val(inittable)) & lastrow
If IsNumeric(Application.Match("Clear", Worksheets(ssheet).Range(taskrng), 0)) Then
hit = Application.Match("Clear", Worksheets(ssheet).Range(taskrng), 0)
valuerng = ConvertToLetter(Val(inittable + 1)) & hit & ":" & ConvertToLetter(Val(inittable + 1)) & lastrow
sumit = WorksheetFunction.Sum(Worksheets(ssheet).Range(valuerng))
'set result
If sumit > okcrit Then
result = "OK"
End If
If sumit >= goodcrit Then
result = "GOOD"
End If
If sumit < poorcrit Then
result = "POOR"
End If
rpt = rpt & vbCr & Worksheets(ssheet).Cells(tablehead, inittable) & "=" & sumit & " [" & result & "]"
Else
rpt = rpt & vbCr & Worksheets(ssheet).Cells(tablehead, inittable) & "=" & "NO MATCH" & " [N/A]"
End If
inittable = inittable + 3
Next x
MsgBox rpt
End Sub

 

[Aladin Akyurek]

Try...

=IF(COUNTIF(TaskRange,"Clear"),LOOKUP(SUMIF(TaskRange,"Clear",ValueRange),{0,10,20},{"Poor","OK","Good"}),"No Match")

Clarification:
Each table is treated separately.
If it is larger than 10, return the word "OK". Otherwise "Poor".
If larger than 20, return the word "Good".
If it couldn't find "Clear", return "No match".