Geek Answers Handbook

Like the SUMIF function in Excel with brackets and "!" sign work

I have a macro in my Excel, and this macro writes a formula for a single cell in my Excel sheet. The code for my macro that does this is as follows:

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

and the formula obtained for cell F10 is as follows:

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

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

+4
source share
2 answers

You have selected failed sheet names.

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

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

! - .

C2 2, B. R1C1 , C , .

C7 7- , G.

'1'! .

C[2] , (C) 2 . H, , F.

R1C1 , VBA . " ", R C , .

R1C1 - 1, 1, , A1. .

R[4]C[-2] . 4 ( 4 ) 2 ( -2 ). , H4, R4C8, R[4]C[-2] F2.

+5

, , ,

=sum($B:$B)

? , Excel, quesiton.

$B:$B - . , enitre B . B , , worksheetname!$B$B, , , 'worksheet name'!$B$B.

, '1'!$B$B B '1'

[]

, ? R1C1. , . , , $B$B, B , (.. , ). R1C1 - Excel, , , , . c2 R1C1 , , (.. B). c[2] , [2] , .

SUMIF

SUMIF excel, , . , , , .

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

'1'!$B:$B - , , $G:$G - (, , , btw...) , , '1'!H:H - , .

, H, B 10, 1

    B   G       H
  ________________ 
1 | 5   '>10   1
2 | 12         2
3 | 13         3

=SUMIF('1'!$B:$B,$G:$G,'1'!H:H) 5 (.. 2 3 H, B 12 13 , 10, 5 , , .

+2

More articles:


All Articles