Dynamic Summing Range

Question

Currently I have a medical spread-sheet with a list of clients that we have serviced. We have 8 different clinical categories which are denoted by different acronyms - HV,SV,CV,WV,CC,OV,TS and GS.

A client can receive multiple therapies i.e. HV,SV,CV - in the background we have a counter mechanism which would increment each of these records by 1.The formula used for this counter is:
=(LEN('Parent Sheet'!F25)-LEN(SUBSTITUTE('Parent Sheet'!F25,'Parent Sheet'!$P$4,"")))/LEN('Parent Sheet'!$P$4)

At the bottom of the sheet we then have a sum which ads up all the treatments that occurred for that week.

Now the tricky part about this is that we have almost a year's worth of data in this sheet but the summing formulas are set as: SUM(COLUMN 6: COLUMN 53) but due to a need to increase the entries beyond this limit, we have to adjust the sum formula. We have 300 SUM Formulas adding up each of the 8 Criteria items and assigning them to the HV,SV,SC,WV etc. counters.

Would we have to adjust this manually one by one or is there a easier way of doing this?

Thank you very much!

Answer 1

To me, I think you should change the sheet layout a little, create a User Defined Function (UDF) and alter the formulas in your Sum rows for efficient row/column adding (to make use of Excel's formula fill). The only issue is that you need to save this as a Macro-Enabled file.

What you need to change in the formulas is to utilize $ to restrict changes in column and rows when the formula fill takes place.

To illustrate in an example, consider:

Assuming the first data starts at row 6, and no more than row 15 (you can use the idea of another data gap on the top). Alter the Sum row titles to begin with the abbreviation then create a UDF like below:

Option Explicit

' The oRngType refers to a cell where the abbreviation is stored
' The oRngCount refers to cells that the abbreviation is to be counted
' Say "HV" is stored in $C16, and the cells to count for HV is D$6:D$15,
' then the sum of HV for that date (D16) is calculated by formula
' `=CountType($C16, D$6:D$15)`

Function CountType(ByRef oRngType As Range, ByRef oRngCount) As Long
    Dim oRngVal As Variant, oVal As Variant, oTmp As Variant, sLookFor As String, count As Long
    sLookFor = Left(oRngType.Value, 2)
    oRngVal = oRngCount.Value ' Load all the values onto memory
    count = 0
    For Each oVal In oRngVal
        If Not IsEmpty(oVal) Then
            For Each oTmp In Split(oVal, ",")
                If InStr(1, oTmp, sLookFor, vbTextCompare) > 0 Then count = count + 1
            Next
        End If
    Next
    CountType = count
End Function

Formulas in the sheet:
Columns to sum are fixed to rows 6 to 15 and Type to lookup is fixed to Column C
D16 | =CountType($C16,D$6:D$15)
D17 | =CountType($C17,D$6:D$15)
...
E16 | =CountType($C16,E$6:E$15)
E17 | =CountType($C17,E$6:E$15)

The way I created the UDF is to lookup and count appearances of a cell value (first argument) within a range of cells (second argument). So you can use it to count a type of treatment for a big range of cells (column G).

Now if you add many columns after F, you just need to use the AutoFill and the appropriate rows and columns will be there.

You can also create another VBA Sub to add rows and columns and formulas for you, but that's a different question.

Answer 2

It's isn't a great idea to have 300 sum formulas.

Name your data range and include that inside the SUM formula. So each time the NAMED data range expands, the sum gets calculated based on that. Here's how to create a dynamic named rnage.

Sorry I just saw your comment. Following is a simple/crude VBA snippet.

Range("B3:F12") is rangeValue; Range("C18") is rngTotal.

Option Explicit

Sub SumAll()
Dim WS As Worksheet
Dim rngSum As Range
Dim rngData As Range
Dim rowCount As Integer
Dim colCount As Integer
Dim i As Integer
Dim varSum As Variant

    'assuming that your said mechanism increases the data range by 1 row

    Set WS = ThisWorkbook.Sheets("Sheet2")
    Set rngData = WS.Range("valueRange")
    Set rngSum = WS.Range("rngTotal")

    colCount = rngData.Columns.Count
    'to take the newly added row (by your internal mechanism) into consideration
    rowCount = rngData.Rows.Count + 1

    ReDim varSum(0 To colCount)

    For i = 0 To UBound(varSum, 1)
        varSum(i) = Application.Sum(rngData.Resize(rowCount, 1).Offset(, i))
    Next i

    'transpose variant array with totals to sheet range
    rngSum.Resize(colCount, 1).Value = Application.Transpose(varSum)

    'release objects in the memory
    Set rngSum = Nothing
    Set rngData = Nothing
    Set WS = Nothing
    Set varSum = Nothing
End Sub

Screen:

Answer 3

You can use named ranges as suggested by bonCodigo or you could use find and replace or you can insert the columns within the data range and Excel will update the formula for you automatically.