Using sumproduct or similar in VBA

[AmyTaylor]

Hi everyone, hope someone can help

I only want to perform this calculation where the sheet name begins
with the number 5 or 6 or the letter T.
I have a number of sheets each contain the same layout of data, with
row 1 containing a header row - however the number of rows containing
data can change.

Each column must be reported unless stated otherwise:
Column A contains a code
Column B contains a code (either "Included" or "Excluded")
Column C contans a code but that doesnt need reported
Column D contains a name relating to thc code in Column C, it needs
reported
Column E to AD contains data which I want to sum.

The sum, probably something like Sumproduct (but i could be wrong)
needs to sum each unique combination of Code A and B (eg:
concatenation).

How do I write this in a function, and preferably in VBA so that it can
be automated for each sheet.

Many many thanks for any help !
Love Amy xxx

 

[Bob Phillips]

Just create a helper column that concatenates A & B (=-A2&B2), and then use

=SUMPRODUCT((C2:C20<>"")/COUNTIF(C2:C20,C2:C20&""))

You just don't put the formula on a sheet not beginning with T 5 or 6, or if
you MUST do it in the formula

=IF(ISNUMBER(MATCH(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1)
)+1,255)),{"5","6","T"},0)),SUMPRODUCT((C2:C20<>"")/COUNTIF(C2:C20,C2:C20&""
)),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[AmyTaylor]

Thanks a million Bob, but can I ask another question ?
Is it possible to do this in VBA so that the process can be automated
?
I know that is asking a lot
Thanks again
Amy xx

 

[Bob Phillips]

Option Explicit

Sub Test()
Const TARGET_CELL As String = "M1"
Dim iLastRow As Long
Dim i As Long
Dim oWS As Worksheet
Dim aryStart
Dim sFormula As String

aryStart = Array("5", "6", "T")
For Each oWS In ThisWorkbook.Worksheets
With oWS
If IsMatch(Left(oWS.Name, 1), aryStart) Then
.Columns(3).Insert
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(2, "C").Resize(iLastRow - 1).Formula = "=A2&B2"
sFormula = "=SUMPRODUCT((C2:C" & iLastRow & "<>"""")/" & _
"COUNTIF(C2:C" & iLastRow & ",C2:C" & iLastRow &
"&""""))"
.Range(TARGET_CELL).Formula = sFormula
End If
End With
Next oWS

End Sub

Private Function IsMatch(pTestValue, arr)
Dim i As Long
Dim sTest As String
For i = LBound(arr) To UBound(arr)
If pTestValue = arr(i) Then
IsMatch = True
Exit Function
End If
Next i
End Function



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)