SumIf with Strings?

Question

This may be a stupid question, and if it is, I apologise. I have a table in excel:

Column a...........Column b

1 property1.......problem x

2 property2.......problemy

3 property3.......problemz

4 property1......problem a

I was wondering if I could use sumif (or any similar formula) to add the problems, referring to a certain property, in one cell. for ex: I would have

Column a...........Column b

1 property1.......problem x problem a

The problem is I can't figure out where to start. I tried using sumif but I get an error. Probably because I'm trying to add strings. I tried to mix a vlookup with sumif but that didn't produce anything too. Im stuck here. Thanks for any help!

Answer 1

I am not 100 % sure, but I think you might need to use VBA for this. You could try to create the following custom function:

1. Create named ranges properties and problems in your sheet.

2. Click ALT+F11 to open VBA editor.

3. Press Insert --> Module

4. Write code

'

Function ConcatIF(Property As String, PropertyRange As Range, ProblemRange As Range) As String
    Dim counter As Integer

    Dim result As String
    result = ""

    Dim row As Range
    counter = 1
    For Each row In PropertyRange.Rows            
        If (Property = row.Cells(1,1).Value) Then
            result = result + ProblemRange.Cells(counter,1).Value + " "
        End If
        counter = counter +1
    Next row
    ConcatIF = result
End Function

As I do not have excel on the machine I am writing this on, I could only test it on another machine, and therefore there could be spelling mistakes in this code.

Ensure that you write the code in the module you created, it cannot be written in a Sheet's code, must be module.

This function can then be called as a regular function, like sum, average and if. Create a unique list of all your properties on another sheet. Properties in column A, and then in column B you can call the cuntion. Assume row 1 is used for headings, write the following and copy down.

=ConcatIF(A2,properties,problems)

NOTE!!!! This code gets out of hand very quickly. It needs to do (number of properties) x (number of property/problem pairs) comparisons, so if this number is huge, it could slow down your sheet.

There could be faster methods, but this was from the top of my head.