Counting Cell or Range

muhammad susanto · Nov 15, 2017

hi all..

how to counting this :

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3

[/td][td]data[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6

[/td][td]after formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7

[/td][td]=E4+1[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

i want the result is =E5 (E4+1) not 4 included mark "="

any help is greatly appreciated..

m.susanto

muhammad susanto · Nov 15, 2017

more clear...

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3

[/td][td]data[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6

[/td][td]after formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7

[/td][td]=E5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9

[/td][td]note : E4+1=E5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10

[/td][td](included mark "=")[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

muhammad susanto · Nov 15, 2017

how to calculate this below :
=E4+1=E5......final answer included mark "=" so =E5

how do it?

Scott Huish · Nov 15, 2017

You can't put =E4+1 to turn it into =E5.
You can offset from E4 if you want and it will return the value from E5 but it won't actually say =E5

=OFFSET(E4,1,0)

muhammad susanto · Nov 15, 2017

hello Scott, thanks a lot...

muhammad susanto · Nov 15, 2017

Scott Huish said:
You can't put =E4+1 to turn it into =E5.
You can offset from E4 if you want and it will return the value from E5 but it won't actually say =E5

=OFFSET(E4,1,0)

hi scott...how to solve this :

Excel 2007

E

3

data

4

a

5

b

6

c

7

d

8

etc..

9

10

after formula

11

a

12

13

14

b

15

16

17

c

Sheet1

Scott Huish · Nov 15, 2017

Assuming text data as in your example

In E11:
=T(IF(MOD(ROWS($A$1:A1),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A1)/3)+3),""))

Copy down.

Book1

E

3

data

4

a

5

b

6

c

7

d

8

9

10

11

a

12

13

14

b

15

16

17

c

18

19

20

d

21

22

23

24

Sheet1

Cell Formulas
Range	Formula
E11	=T(IF(MOD(ROWS($A$1:A1),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A1)/3)+3),""))
E12	=T(IF(MOD(ROWS($A$1:A2),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A2)/3)+3),""))
E13	=T(IF(MOD(ROWS($A$1:A3),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A3)/3)+3),""))
E14	=T(IF(MOD(ROWS($A$1:A4),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A4)/3)+3),""))
E15	=T(IF(MOD(ROWS($A$1:A5),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A5)/3)+3),""))
E16	=T(IF(MOD(ROWS($A$1:A6),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A6)/3)+3),""))
E17	=T(IF(MOD(ROWS($A$1:A7),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A7)/3)+3),""))
E18	=T(IF(MOD(ROWS($A$1:A8),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A8)/3)+3),""))
E19	=T(IF(MOD(ROWS($A$1:A9),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A9)/3)+3),""))
E20	=T(IF(MOD(ROWS($A$1:A10),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A10)/3)+3),""))
E21	=T(IF(MOD(ROWS($A$1:A11),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A11)/3)+3),""))
E22	=T(IF(MOD(ROWS($A$1:A12),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A12)/3)+3),""))
E23	=T(IF(MOD(ROWS($A$1:A13),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A13)/3)+3),""))
E24	=T(IF(MOD(ROWS($A$1:A14),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A14)/3)+3),""))

muhammad susanto · Nov 15, 2017

HI SCOTT...can you explained where came from number 3?

Scott Huish · Nov 15, 2017

You're putting data in every 3rd row.

muhammad susanto · Nov 15, 2017

HI SCOTT..
Please help me for this problem :
Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

111

[/td][td][/td][td][/td][td]DATA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

112

[/td][td][/td][td][/td][td]5020499999-08[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

113

[/td][td][/td][td][/td][td]5020405999-03[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

114

[/td][td][/td][td][/td][td]5020405999-01[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

115

[/td][td][/td][td][/td][td]5020403002-03[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

116

[/td][td][/td][td][/td][td]5020403002-02[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

117

[/td][td][/td][td][/td][td]5020403002-01[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

118

[/td][td][/td][td][/td][td]5020401999-42[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

119

[/td][td][/td][td][/td][td]5020401999-36[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

120

[/td][td][/td][td][/td][td]5020401999-23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

121

[/td][td][/td][td][/td][td]5020401999-22[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

122

[/td][td][/td][td][/td][td]5020401999-21[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

123

[/td][td][/td][td][/td][td]5020401999-19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

124

[/td][td][/td][td][/td][td]5020401999-17[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

125

[/td][td][/td][td][/td][td]5020401999-09[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

126

[/td][td][/td][td][/td][td]5020401999-07[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

127

[/td][td][/td][td][/td][td]5020401999-05[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

128

[/td][td][/td][td][/td][td]5020401999-02[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

129

[/td][td][/td][td][/td][td]5020303003-11[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

130

[/td][td][/td][td][/td][td]5020303002-06[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

131

[/td][td][/td][td][/td][td]5020401999-09[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

132

[/td][td][/td][td][/td][td]5020401999-07[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

133

[/td][td][/td][td][/td][td]5020401999-05[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

134

[/td][td][/td][td][/td][td]5020401999-02[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

135

[/td][td][/td][td][/td][td]5020303003-11[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

136

[/td][td][/td][td][/td][td]5020303002-06[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

137

[/td][td][/td][td][/td][td]5020401999-23[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

138

[/td][td][/td][td][/td][td]5020401999-22[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

139

[/td][td][/td][td][/td][td]5020401999-21[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

140

[/td][td][/td][td][/td][td]5020401999-19[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

141

[/td][td][/td][td][/td][td]5020401999-22[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

142