Conditional VBA sum, displaying =SUM(...) in selected cell

[CedricMattelaer]

My problem is that I want to have the conditional sum in a specific cell (say, A1) but written as a normal sum. An example to clarify:

[C343:HD343] looks like this:
1, 2, 3, ..., 15,1, 2, ... 15, ...
Now I want to sum up all the values in a row with the same dimensions, say [C344:HD344] when the value of [C343:HD343] equals a specific number, but I want the "SUM" formula in the cell, not the actual result.

Like this

1 2 3 1 2 3 1 2 3
-----------------
4 5 7 2 4 3 2 3 4
3 5 6 7 2 9 3 4 2

Now I want to sum up (eg in cell A1) the values of the row underneath but only when the cell in the upper row equals 2.
The result will be 5+4+3=12 for row 1 and 5+2+4 for row 2 but I need to have =SUM(...) on my worksheet (not SUMIF!)

If I could use SUMIF, the formula in an excel cell would be for value=2: =SUMIF(C343:HD343, "=2", C344:HD344).

Is there a way to do this?

I tried something like

Range("A1").Formula = "=SUM(" & if Cells(343,3+i)=2 then ...

But I got stuck and don't know how to continue.
Thanks for any help you can give me!

 

[jasonb75]

Not tested

Range("A1").FormulaArray = "=SUM(IF(R343C" & 3 + i & "=2,R344C" & 3 + i &"))"

This only covers 1 column, you need to edit the 2nd instance of 3 + i to cover the full range of columns required.

 

[Weaver]

Dunno if this is what you're after, but it's how I interpreted your query.

Sub test()
    Dim a As Range
    For Each c In Range("C344:HD344")
        If c.Value = 2 Then
            If a Is Nothing Then
                Set a = c
            Else
                Set a = Union(a, c)
            End If
        End If
    Next
    If Not a Is Nothing Then
        Range("A1").Formula = "=sum(" & a.Offset(1).Address & ")"
    Else
        Range("A1").Formula = "=sum(0)"
    End If
End Sub

PS how come you can't use sumif?

 

[CedricMattelaer]

Not tested

Range("A1").FormulaArray = "=SUM(IF(R343C" & 3 + i & "=2,R344C" & 3 + i &"))"

Unfortunately, I can't use the "IF" statement either.

@ Jason: thanks, this seems to be working. I'll post something if some problem persist. I can't use the SUMIF or IF function bacause the add-in I use to solve the linear program doesn't support those functions, only supports SUM

 

[Weaver]

Have you tried the code from post #3?

 

[CedricMattelaer]

Have you tried the code from post #3?

Yes, I have tried it, and it works. I had to change the C344:HD344 to C343:HD343 though.
Where in the program is it included what row he sums up?
Is it

" & a.Offset(1).Address & ")

?
Does that mean that he goes down one row same column to sum up?

Thanks!

 

[CedricMattelaer]

Hey, tried it out and I can change the rows indeed by changing the " & a.Offset(1).Address & ").
This is wonderful!
Thanks a lot

 

[Weaver]

Hey, tried it out and I can change the rows indeed by changing the " & a.Offset(1).Address & ").
This is wonderful!
Thanks a lot

Looks like you figured it out. Slight typo was the result of me testing it on rows 3 & 4 because it saved scrolling halfway down the sheet.

Thanks for the feedback.

W

 

[CedricMattelaer]

Just a small remark, as I tried to apply it to a whole matrix.
I tried it with loops but that gives a strange effect: the sums are fine in the beginning but then start to increase row wise. Instead of just taking the cell with value of the row above =2, all the next cells are also summed up, resulting after a while in the sum being always just the whole row summed up.

This is the code I used:

Sub test()

For i = 0 To 273
For j = 0 To 14
    Dim a As Range
    For Each c In Range("C343:HD343")
        If c.Value = 1 + j Then
            If a Is Nothing Then
                Set a = c
            Else
                Set a = Union(a, c)
            End If
        End If
    Next
    If Not a Is Nothing Then
        Worksheets("Oefening").Cells(9 + i, 53 + j).Formula = "=sum(" & a.Offset(1 + i).Address & ")"
    Else
        Worksheets("Oefening").Cells(9 + i, 53 + j).Formula = "=sum(0)"
    End If

    
    
Next j
Next i
End Sub

Could it be due to the fact that the value of a is continuously increasing?

Thanks

 

[CedricMattelaer]

Now, the exact same code yields nothing but =sum(0) in the destination table
BA9:BO281...