multiply data in every 3rd row on a sheet

  • Thread starter Thread starter Elsmith
  • Start date Start date
Enter this array* formula

=PRODUCT(IF(LEN(ROW(A2:A100)/3)=1,A2:A100,"X"))

Adjust range location/size as desired.

*Use Ctrl+Shift+Enter to confirm formula
 
Say in A1 thru A21 we have:

16
16
22
27
14
11
4
1
9
1
3
24
6
5
24
28
30
4
18
7
28

and we want to multiply every third row by 7 (that is rows 1, 4,7,...).

In B1 thru B3 enter:
7
1
1

Copy B1 thru B3 and paste to B4 thru B21.

In C1 enter:
=A1*B1 and copy down.

We see:

16 7 112
16 1 16
22 1 22
27 7 189
14 1 14
11 1 11
4 7 28
1 1 1
9 1 9
1 7 7
3 1 3
24 1 24
6 7 42
5 1 5
24 1 24
28 7 196
30 1 30
4 1 4
18 7 126
7 1 7
28 1 28

So every third row has been multiplied by 7.
 
Elsmith said:
How do I multiply numbers from every 3rd cell in a worksheet?
Click to expand...

To sum every 3rd cell in A2:A100, starting with the first cell in the
range, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

=PRODUCT(IF(A2:A100<>"",IF(MOD(ROW(A2:A100)-ROW(A2)+0,3)=0,A2:A100)))

To sum every 3rd cell, starting with the first occurrence of nth,
replace...

+0

with

+1

Hope this helps!

http://www.xl-central.com
 
Back
Top