Help Adding Every 9th row...

[Takeadoe]

I'm experimenting with the following function
=SUM(IF(MOD(ROW(Test3),3)=0,Test3,0)) - and can't figure out what it is
doing. When I change the 3 (after Test3),) to 2 and 4, I assume that
I'm asking it to sum every 2nd and 4th number (3rd with a value of 3).
That's not what I'm getting. Ultimately, what I want to do is add
value 1, 10, and 19 together. Would that be every 9th record? I have
a table where I need to add the 1st, 10th, 19th, 28th...

Can I do that with this formula or a slight modification of it?


Test3=Is simply a defined range.

=SUM(IF(MOD(ROW(Test3),3)=0,Test3,0))

With the formula as it is applied to the TEST3 range below, I get 19.

I don't see how it is getting that.

Any help would really be appreciated.

Mike

A TEST3
1 7
2 9
3 0
4 2
5 3
6 11
7
8
9
10 0
11 0
12
13 1
14 0
15 1
16
17
18
19 0
20 0
21
22 7
23 19
24 7

 

[Dav]

You are obviously using it as an array to get 19 but the sum o
rows3,6,9,12,15,18,21,24 =0+11+0+0+1+0+7=19 so that is the correc
answer

if you want every 9th value starting at 1

=SUM(IF(MOD(ROW(Test3)+8,9)=0,Test3,0))

Otherwise you count the 9th 18th 27th etc

Remember to enter it as an array

Regards

Da

 

[Bernard Liengme]

If TEST3 is a single column then =SUMPRODUCT(--(MOD(ROW(Test3),9)=1),Test3)
sums the 1st, 10th, 19th,... entry

But it will not work when TEST3 had more than 1 column
In that case, this will work
=SUM(IF(MOD(ROW(A1:A50),9)=1,A1:B50))
provided it is entered as an array formula using SHIFT+CTRL+ENTER
You could use
=SUM(IF(MOD(ROW(Test1),9)=1,Test2))
where Test1 is A1:A50 (or the like) and Test2 is A1:B50 (or similar)
best wishes

 

[Bob Phillips]

If you want the rows 1,10,19,28, etc, use

MOD(ROW(Test3),9)=1

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Takeadoe]

Dav - when I apply your formula
=SUM(IF(MOD(ROW(Test3)+8,9)=0,Test3,0)) to test3 in my original post, I
get 22. If you sum the values for 1, 9, and 18 you get 7 - not 22.
What am I missing here?

Mike

 

[Takeadoe]

Folks - just a follow up to my earlier response - I did a bit of
playing around and found out that the following is actually taking
place when I use the formula provided by Dav.

1) TEST3 - my range actually reflects cell values from C6:C29

2) The only way that I could come up with 22 using the numbers I
provided was to sum the values in C10, C19, and C28 (3, 0, and 19,
respectively).

3) Thus, it would appear that it is ignoring the named range TEST3

Again, what I'm ultimately after is 1, 10, 19, 28, 37, 46, etc...

 

[Takeadoe]

Bob - When I use your formula, all I get is "False" returned to the
cell. Any ideas?

Mike

 

[Takeadoe]

Guys - Just one more note on this - what I found was that the only way
I could get this to sum 1, 10, 19, 28, etc is to insert rows so that my
first record starts on 10 - apparently the function will not add the
first number in the range - it adds 9 - the step indicated - and begins
there.

 

[Ragdyer]

So, if your "Test3" range does *not* include all the rows you wish to total,
then,
either re-size it,
OR
DON'T use it!

Using cell references *only*, try this:

=SUMPRODUCT((MOD(ROW(C1:C100),9)=1)*C1:C100)

 

[Bob Phillips]

I only gave the relevant part, you need to replace it in your formula.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Dav]

You could have adjusted the formula to get it to sum your 1st 10th 19t
28th etc. In your original example people will have assumed that you
numbering down the left was row number, not a range starting in row6

SUM(IF(MOD(ROW(Test3)+8,9)=0,Test3,0))

the row function returns the row on the spread sheet, if your 1st ro
was actually row 6 on the spreadsheet.

SUM(IF(MOD(ROW(Test3)+3,9)=0,Test3,0))

as 6+3=9 ans so mod(9,9)=0 and then for every subsequent 9 rows

Regards

Da

 

[akyurek]

=SUMPRODUCT(--(MOD(ROW(C6:C29)-ROW(C6)+0,9)=0),C6:C29)

would some every 9th figure starting with the figure in C6.

=SUMPRODUCT(--(MOD(ROW(C6:C29)-ROW(C6)+1,9)=0),C6:C29)

would some every 9th figure starting with the first 9th figure in
C6:C29.

 

[Takeadoe]

Dav - I really appreciate your help and your patience as I muddle my
way through this foreign software! With some experimenting, I got it
to work perfect... well almost. In 2 of the 6 columns that I applied
this to, I got #value returned. I'm guessing that perhaps there is
text in there that appears to be a number. Do you think that may be
the case? If so, is there an easy way to locate the rogue entries and
fix them? I'm sorry to be such a pest!

Mike

 

[Takeadoe]

Thank you all very much! I really do appreciate your help!

With some experimenting, I got it to work perfect... well almost. In 2
of the 6 columns that I applied this to, I got #value returned. I'm
guessing that perhaps there is text in there that appears to be a
number. Do you think that may be the case? If so, is there an easy
way to locate the rogue entries and fix them? I'm sorry to be such a
pest!

Mike

 

[Takeadoe]

Bob - I really appreciate your help and your patience as I muddle my
way through this foreign software! With some experimenting, I got it
to work perfect... well almost. In 2 of the 6 columns that I applied
this to, I got #value returned. I'm guessing that perhaps there is
text in there that appears to be a number. Do you think that may be
the case? If so, is there an easy way to locate the rogue entries and
fix them? I'm sorry to be such a pest!

Mike

 

[Dav]

If the values look like numbers but are text try

SUM(IF(MOD(ROW(Test3)+3,9)=0,(Test3)*1,0))

the *1 will convert the evaluation of the text as numbers and so sum
them, but i do not think this is your problem as they would be excluded
from the calculation, I do not think they would produce the value error

To find the text values would take more time but you could look at the
istext function

Regards

Dav

 

[Dav]

change SUM(IF(MOD(ROW(Test3)+3,9)=0,Test3,0))

to SUM(IF(MOD(ROW(Test3)+3,9)=0,(Test3)*1,0))

Would now sum your text values as well, but if it is not there it would
just exlcude them from the calculation. The value error is not caused by
this.

Regards

Dav

 

[dbahooker]

if you kept your data in a database; then it would be easier to put
headers / footers / subtotals.

excel shouldn't be used for database reporting; it doesn't have enough
functionality

-Aaron

 

[greaseman]

Aaron strikes again! Where in the posting do they talk about headers
footers or subtotals? What planet are you from, Aaron??

Sheesh! And when you do reply to someone Aaron, you never ever provid
any contribution, only troll rantings!

After seeing so many of your postings, do you really think people pa
attention to you any more

 

[dbahooker]

going through and adding something every 9th record?

i mean seriously


get real


having a bunch of random CRAP in the middle of your DATA is only going
to make it more complex going down the road.