SUM of offset cells from an offset results

Question

I need to sum the results of the one highlighted in red which I got from the startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))

The results from the code does not have a fixed location as the listed cities in Column C is different everyday and in every sheet.

The 1st Results of total is from the code:

Public Sub count()

Dim lastCell As String
Range("C2").Select

Selection.End(xlDown).Select
lastCell = ActiveCell.Address

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=counta(C2:" + lastCell + ")"
End Sub

The 2nd results of total is from the code: citiesCount = countRange.Rows.Count . The problem with this code is that if a city is not listed in my array, it will still count it.

Here is the full code.

Public Sub B1_Manual_CountLocations()

Dim wb As Workbook
Dim ws As Worksheet
Dim lastCell As String
Dim countRange As Range

count 'Call the function to count the total of cities 

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet 'Change as appropriate

Set countRange = ws.Range(Cells(2, "C"),Cells(ws.Range("C2").End(xlDown).Row, "C"))

Dim Cities()
Cities = Array("Armonk", "Bratislava", "Bangalore", "Hong Kong", "Mumbai", Zurich")
Dim city As Long
Dim counter As Long
Dim startRange As Range

Set startRange = ws.Cells(ws.Range("C2").End(xlDown).Row, "C").Offset(2, 0)

counter = 2

Dim citiesCount As Long
citiesCount = ((countRange.Rows.count) - 1) 'new line to hold total number of cities


For city = LBound(Cities) To UBound(Cities)

  If Application.WorksheetFunction.CountIf(countRange, Cities(city)) > 0 Then
startRange.Offset(counter, -1) = Application.WorksheetFunction.CountIf(countRange, Cities(city)) / citiesCount 'new line to calculate proportion of total
startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))
startRange.Offset(counter, 1) = Cities(city)

counter = counter + 1 

  End If


Next city
startRange.Offset(counter + 1, 0) = "Total:" & citiesCount 'count the total number of city

End Sub

I already checked SUM of offset cells that contain a value but this is not clear to me.

You would not use citiesCount = ((countRange.Rows.count) - 1) 'new line to hold total number of cities as you may have two or more cities in the range you don't want to count. — QHarr, Nov 17 '17 at 08:45
what code should i use to count the sum of the results highlighted in red? I would agree on not using `citiesCount = ((countRange.Rows.count) - 1)` as it will count even if the city is not on my list of array. — Jonathan, Nov 17 '17 at 09:24

QHarr · Accepted Answer · 2017-11-17T09:23:29.213

You would need to add perhaps a function to check if the values in the count range exist in your array, if it does then for each match add 1 to your total cities count.

Using a function by Jimmy Pena you would have something like the following:

Option Explicit

Public Sub B1_Manual_CountLocations()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim countRange As Range
    Dim cell As Range

    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet                      'Change as appropriate

    Set countRange = ws.Range(ws.Cells(2, "C"), ws.Cells(ws.Range("C2").End(xlDown).Row, "C")) 'explicit worksheet reference

    Dim Cities()
    Cities = Array("Armonk", "Bratislava", "Bangalore", "Hong Kong", "Mumbai", "Zurich")
    Dim city As Long
    Dim counter As Long
    Dim startRange As Range
    Dim citiesCount As Long

    citiesCount = 0

    For Each cell In countRange

        If IsInArray(cell.Value, Cities) Then
            citiesCount = citiesCount + 1
        End If

    Next cell

    Set startRange = ws.Cells(ws.Range("C2").End(xlDown).Row, "C").Offset(2, 0)
    counter = 2

    For city = LBound(Cities) To UBound(Cities)

        If Application.WorksheetFunction.CountIf(countRange, Cities(city)) > 0 Then

            startRange.Offset(counter, -1) = Application.WorksheetFunction.CountIf(countRange, Cities(city)) / citiesCount 'new line to calculate proportion of total
            startRange.Offset(counter, 0) = Application.WorksheetFunction.CountIf(countRange, Cities(city))
            startRange.Offset(counter, 1) = Cities(city)

            counter = counter + 1

        End If

    Next city

    startRange.Offset(counter + 1, 0) = "Total:" & citiesCount 'count the total number of city

End Sub

'https://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

with larger city numbers you might want to read the countRange into an array and then loop that array comparing against the other array (Cities) adding one to citiesCount for each match. Will be faster. — QHarr, Nov 18 '17 at 04:34

SUM of offset cells from an offset results

1 Answers1