How SUMIF function in Excel with brackets and "!" sign works

Question

I have a Macro in my Excel and this Macro writes a formula to one cell in my Excel sheet. The code of my Macro that does this is like below:

 Range("F10").Select
 ActiveCell.FormulaR1C1 = "=SUMIF('1'!C2,C7,'1'!C[2])"

and the Formula produced for the F10 cell is like below:

=SUMIF('1'!$B:$B,$G:$G,'1'!H:H)

Can someone help me understand this code? What does '1' , ! , [] and ... do exactly? In general I want to know what is the output of " =SUMIF('1'!$B:$B,$G:$G,'1'!H:H)" and why the macro generates this formula?

Answer 1

You have chosen unfortunate sheet names.

"=SUMIF('1'!C2,C7,'1'!C[2])"

The '1' is the sheet name. Sheet names can be wrapped in single quotes, but you only need to do that if the sheet name contains a space.

The ! is a separator between sheet name and cell reference.

C2 means column 2, which is column B. You are using R1C1 syntax for the formula, so the C identifies the following number as related to a column.

C7 means the 7th column, so, column G.

'1'! again means the sheet name and the separator.

C[2] means from the current column (the C) go 2 to the right. If the formula returns column H for that, it means that the current cell must be in column F.

R1C1 referencing is not widely used in worksheets these days, but in VBA it makes relative cell referencing very easy. The brackets mean "relative to the current location" and the R or C will determine whether it's a row or column that needs to be traversed.

R1C1 is Row 1, column 1, in other words, cell A1. This is an absolute reference.

R[4]C[-2] is a relative reference. It will be evaluated based on the current cell and from that position will add 4 rows (positive value 4 for rows) and subtract 2 columns (negative value -2 for columns). So, if the current cell is H4, which translates to R4C8, a reference to R[4]C[-2] means F2.