I have a spreadsheet in Numbers and I want to add every nth row. I want to add every 24th row in my column of ~4500 elements, and do that 24 times since this is a time table. I also have multiple columns with different items that need to be added as well.
How do I do that?
Numbers-OTHER, Mac OS X (10.7.5)
Hi o',
Here's an example showing one way:
The listed values are in column B. The specific values were chosen to provide easily recognizable totals while testing.
The period and starting point are entered in D2 and D3 respectively. These are used to generate the index list in column A.
SUMIF is used in D4 to calculate the total of all rows of column B where Column A contains a zero.
Formulas:
A2, and filled down: =MOD(ROW()+$D$2-1-$D$3,$D$2)
D4: =SUMIF($A,"=0",$B)
Regards,
Barry
Hi o',
Here's an example showing one way:
The listed values are in column B. The specific values were chosen to provide easily recognizable totals while testing.
The period and starting point are entered in D2 and D3 respectively. These are used to generate the index list in column A.
SUMIF is used in D4 to calculate the total of all rows of column B where Column A contains a zero.
Formulas:
A2, and filled down: =MOD(ROW()+$D$2-1-$D$3,$D$2)
D4: =SUMIF($A,"=0",$B)
Regards,
Barry
Barry,
Elegant solution! Mine is so similar I am somewhat reluctant to post it because I don't want to take away from what is a solid approach. There are just a couple of points that I think might be interesting to O.
(I have, as I usually do, separated the data table from the summary or control table. I call my two tables for this example Data and Control.)
The two points are:
1. In a conditional sum or count function, the equal sign can be omitted, as it is the default comparison operator. (But, it doesn't hurt to include it.)
2. It is not necessary to use the absolute column notation in either the comparison or value reference argument of the conditional sum or count function.
In the screen shot that follows, the formula in Column A of the table Data is:
=MOD(ROW()-Control :: $2:$2-1, Control :: $1:$1)
and, the forumula for the SUM is:
=SUMIF(Data :: A, 0, Data :: B)
Jerry
Hi ousmanemariko,
I see that you want to add every 24th row in a time table. To me, that means each hour of a day. Am I correct?
Also, you have multiple columns with different items
Here is a screen shot of a table that I think you have in mind:
Please ignore the silly numbers in Column C 'Value 1' and Column D 'Value 2'. I made them up 🙂.
Column A is Date. Column B is a number for each hour. Midnight is the number 0. 1:00 am is the number 1.
Please do not worry about the green fill in A27. That was done by Inspector > Graphic > Fill > Color fill > Colors. (For my benefit!)
I hope this table helps you (and me) to find an answer to your question.
Regards,
Ian.
Thanks, Ian! I kind of figured it out. What I did was make a row with 0-23 repeating. I thought there was a way to compare just the number of the row, but I guess not. Or if there is, it was probably not the best way to do so.
Thanks again!
Hi ousmanemariko,
I thought there was a way to compare just the number of the row
Yes, there is a way to find the row number:
Cell B2 contains this formula:
=ROW(A2)
Then Fill Down
In Numbers, click on the Function button in the Toolbar and choose Show Function Browser. In the Search box, type row.
Regards,
Ian.
How do I add every nth row?
