CODEWITHSUNDEEP
excelvbaexcel-formula
klaus
klaus

Reputation: 826

Sum values on column A based on a string criteria in column B

I have the following columns:

A       B
23      75001
42      94
1       13
3       75002
4       12

I would like to sum values of column A if the two first digits of the number in column B is matching 75 or 12.

In this example, the result would be 23 + 3 + 4 = 30.

I tried to use =SUMIFS but it looks like I can only use a criteria based on a integer ...

Do you have an idea how to do this ?

EDIT : I am trying to make a VBA macro

Upvotes: 1

Views: 233

Answers (3)

FaneDuru
FaneDuru

Reputation: 42256

Try the next function, please:

Function countOccLeft(arr As Variant, Cr1 As String, Cr2 As String) As Long
   Dim dict As Object, i As Long: Set dict = CreateObject("Scripting.Dictionary")
   
   For i = 1 To UBound(arr)
     If Left(arr(i, 2), 2) = Cr1 Or Left(arr(i, 2), 2) = Cr2 Then
        If Not dict.Exists("key_sum") Then
            dict.Add "key_sum", arr(i, 1)
        Else
            dict("key_sum") = dict("key_sum") + arr(i, 1)
        End If
     End If
   Next i
   countOccLeft = dict("key_sum")
End Function

It can be called in this way:

Sub testCountOccL()
  Dim sh As Worksheet, arr As Variant, strSearch As String, lastRow As Long
  
   Set sh = ActiveSheet
   lastRow = sh.Range("A" & Rows.Count).End(xlUp).Row
   arr = sh.Range("A2:B" & lastRow).Value
   
   Debug.Print countOccLeft(arr, "75", "12")
End Sub

Upvotes: 0

JvdV
JvdV

Reputation: 75990

You could use:

=SUMPRODUCT(IF(ISNUMBER(SEARCH({"|75";"|12"},"|"&B1:B5)),A1:A5,0))

Though, if you'd have ExcelO365, you could use SUM() instead. If you need this to be VBA you could mimic this quite easily. Don't let it be mistaken, the other answer is the go-to method, however, this way you could easily add more criteria if need be.

To mimic this in VBA you could use an array and Select Case if you would need to add more criteria in the future:

Sub Test()

Dim lr As Long, x As Long, sm As Double, arr As Variant
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")

With ws
    lr = .Cells(.Rows.Count, 2).End(xlUp).Row
    arr = .Range("A1:B" & lr).Value
    For x = LBound(arr) To UBound(arr)
        Select Case Left(arr(x, 2), 2)
            Case "75", "12": sm = sm + arr(x, 1)
        End Select
    Next
    Debug.Print sm
End With

End Sub

Upvotes: 1

BigBen
BigBen

Reputation: 50162

One option using SUMPRODUCT:

=SUMPRODUCT(A1:A5*((--LEFT(B1:B5,2)=75)+(--LEFT(B1:B5,2)=12)))

enter image description here

Similarly, using FILTER if you have access to it:

=SUM(FILTER(A1:A5,((--LEFT(B1:B5,2)=75)+(--LEFT(B1:B5,2)=12))))

Even shorter:

=SUMPRODUCT(A1:A5*(LEFT(B1:B5,2)={"75","12"}))

or if you don't want to put quotes around the numbers

=SUMPRODUCT(A1:A5*(--LEFT(B1:B5,2)={75,12}))

though keeping the quotes around 75 and 12 is a better approach if you have one or more blank cells in B1:B5.

Upvotes: 3

Related Questions