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!

 

[CedricMattelaer]

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

Ok, that's no problem anymore but the previous one still is.

 

[Weaver]

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

I put that bit in so you don't get an error if no cells match your condition. If you can see where this is done in the code, you might wish to change it.

As for the other issue, move the dim statement outside the 2 loops and replace it with

 set a = nothing

see if that helps

 

[CedricMattelaer]

Hello,

I just tried it when I read your post. Yes it's working fine now!