Equation to sum up the cell values of only even (or odd) columns in a row.

Question

Equation to sum up the cell values of only even (or odd) columns in a row.

Hello all. I'm looking to find an equation in Numbers that will give me a sum of an entire row, excluding either the even or the odd column cells. Here is what I have so far:

IF(MOD(COLUMN(C3);2)=1;"Yes";"No")

This works (for example, with cell C3) to determine if the cell is odd or even. Then, separately I have this:

SUMIF(B3:F3;MOD(COLUMN(B3:F3);1))

Though this is not working.

Does anyone have any tips? Been reading the documentation and hunting the web for awhile with no luck. 😟

iWork Numbers-OTHER

Posted on Jun 1, 2015 3:02 AM

Reply

Answer 1

Jun 1, 2015 4:50 AM in response to Dylan Cromwell

The easiest way is to use another row that only includes the values from the odd (or even) columns... like this:

C4=IF(MOD((COLUMN()−3), 2)=1, C3, "")

this is shorthand for... select cell C3, then type (or copy and paste from here) the formula:

=IF(MOD((COLUMN()−3), 2)=1, C3, "")

select cell C4, copy

select cells C4 thru the end of row 4, paste

now sum the cells in row 4

Reply

Answer 2

Jun 1, 2015 6:22 AM in response to Wayne Contello

Hey Wayne, thanks so much for your answer.

Unfortunately this is a bit convoluted for me. It would require that every row from which I need to sum alternating values would need to have an additional, basically identical row underneath. Further, if I want to also combine totals from multiple rows it would cause another problem (I would have to skip odd or even rows as well).

It's really hard to believe there isn't an equation to do this. I got very very close with something like this:

SUMPRODUCT(MOD(COLUMN(B3:F3);2);B3:F3)

But still not quite.

Any other ideas?

Reply

Answer 3

Jun 1, 2015 6:54 AM in response to Dylan Cromwell

Dylan,

Post a screenshot of what you have. I answered the question as posted. It is difficult to propose a solution with an incomplete description of the problem.

Here is another shot in the dark:

Table 1 (on the top) contains the data you want to sum (even or odd columns)

The table "EvenOddTable" (on the bottom) contains two arrays-- one for even one for odd.

In the EvenOddTable....

C1=IF(MOD(COLUMN()−3, 2)=IF($A1="Even", 0, 1), 1, 0)

select C1, copy

select C1 thru the end of I2, paste

you will have to resize this table to match the size of "Table 1"

enter "Even" in A1

enter "Odd" in A2

In "Table 1"

B3=SUMPRODUCT(C3:I3, EvenOddTable::C$1:I$1)

select cell B3, copy

select cells B3 thru B5 (in this example), paste

Reply

Answer 4

Jun 1, 2015 7:24 AM in response to Wayne Contello

The table "EvenOddTable" is a single table you can hide or move out of the way

Reply

Answer 5

Jun 1, 2015 7:55 AM in response to Wayne Contello

Sorry for not explaining in more detail. Let me give a simple example from Excel:

Here I have 4 values in Row 1. Column A and C are obviously the odd columns, and B and D the even.

Cell F1 adds up the odd column cells and G1 adds up the even column cells.

Here are the equations to do so:

As you can see, these equations do not require extra tables, any linking, or extra rows.

Now in Numbers, the exact same commands exist but work differently. When putting in this same equation I get the error that the second range cannot be "used as a single value". Reading the documentation of SUMPRODUCT, I then split the equation to be like so:

SUMPRODUCT(MOD(COLUMN(B3:F3);2);B3:F3)

Which specifies that arguments to SUMPRODUCTS must be the same length. Thus, if I could have MOD(COLUMN(B3:F3);2) actually return its values as a range then everything would work. Reading the documentation on COLUMN it looks like the function only accepts a single cell, which is weird.

I'm so close - I just don't know how to get a range (array) of numbers which would represent even or odd for the same range as I'm passing over in SUMPRODUCT.

Any ideas there?

(And sorry I am not showing explicit screenshots of numbers - the work I am doing is financial and cannot be shared, plus, I'm not sure the specifics matter - this type of thing is immediately easy to solve in Excel, I can't imagine there is not a way in Numbers also!)

Reply

Answer 6

Jun 1, 2015 7:59 AM in response to Dylan Cromwell

Numbers does NOT support arrays like Excel does.

I have now provided two solutions.

You can share an example by duplicating the table and making up some data.

Generally... the guidance I provide is if one tool does not do the job you should not use that tool in favor of one that does.

Reply

Answer 7

Jun 1, 2015 8:01 AM in response to Wayne Contello

Alright, thanks for all your time and effort here, Wayne, it's very much appreciated.

I'm not going to duplicate the table for now, because I don't think it will help. Any solution that is not a single equation is going to cause too much overhead for my setup.

I think your general guidance is the best path - unfortunate as it is because I really love Numbers but I just don't understand why it fails to do something as simple as this. If it was a tiny bit more powerful it would really give Excel a run for its money.

Thanks again.

Reply

Answer 8

Jun 1, 2015 8:12 AM in response to Dylan Cromwell

Dylan,

I provided a solution that uses a single formula AND only one extra table for ALL variations. The table "EvenOddTable" can exist for all other formulas that use need an even or odd mask.

Personally, I do no think that the one extra table is onerous.

Did you try the example I provided?

Here are some screenshots showing the cells the sum product references:

What this is showing is that each sum uses a specific row of the data table ("Table 1") while using the same (common) row of the "EvenOddTable". The "EvenOddTable" can be in any position or sheet of the same document.

Reply

Answer 9

Yellowbox

Level 7

20,389 points

Jun 1, 2015 8:29 AM in response to Dylan Cromwell

Hi Dylan,

Another approach. Someone with better Numbers skills than I will show you how to combine the formulas.

Values are in Row 1

Formula in C2 (and Fill Right)

=COLUMN()

Formula in C3 (and Fill Right)

=C2÷2 (divided by, in case the divide sign does not show in this reply).

Formula in C4 (and Fill Right)

=IF(C3>INT(C3),"Odd","Even")

Formula in Header Cell B5

=SUMIF($4,"=Even",$1)

Fill down to B6 and change "=Even" to "=Odd"

Hide Rows 2, 3, 4

Or move Columns A5 to B6 to another table (Menu > Table > Header Rows > 1 to allow you to delete Column C in the new table).

Formula in Table 2 B1 becomes

=SUMIF(Table 1::$4,"=Even",Table 1::$1)

Formula in Table 2 B2 becomes

=SUMIF(Table 1::$4,"=Odd",Table 1::$1)

Regards,

Ian.

Reply

Answer 10

Jun 1, 2015 8:28 AM in response to Wayne Contello

Ian provided an inspiration... include the even/odd array in the data table:

C4=SUMPRODUCT(C4:I4, $C$2:$I$2)

select C4, copy

select C4 thru the end of column I, paste

Reply

Answer 11

Hiroto

Level 5

7,461 points

Jun 1, 2015 8:46 AM in response to Dylan Cromwell

Hello

You may specify constant array such as {1,0,1,0,1,0} in SUMPRODUCT() if it helps.

E.g.,

Table 1 (in English, decimal-period locale) A1 n1 A2 =RANDBETWEEN(0,10) A3 =RANDBETWEEN(0,10) A4 =RANDBETWEEN(0,10) A5 =RANDBETWEEN(0,10) B1 n2 B2 =RANDBETWEEN(0,10) B3 =RANDBETWEEN(0,10) B4 =RANDBETWEEN(0,10) B5 =RANDBETWEEN(0,10) C1 n3 C2 =RANDBETWEEN(0,10) C3 =RANDBETWEEN(0,10) C4 =RANDBETWEEN(0,10) C5 =RANDBETWEEN(0,10) D1 n4 D2 =RANDBETWEEN(0,10) D3 =RANDBETWEEN(0,10) D4 =RANDBETWEEN(0,10) D5 =RANDBETWEEN(0,10) E1 n5 E2 =RANDBETWEEN(0,10) E3 =RANDBETWEEN(0,10) E4 =RANDBETWEEN(0,10) E5 =RANDBETWEEN(0,10) F1 n6 F2 =RANDBETWEEN(0,10) F3 =RANDBETWEEN(0,10) F4 =RANDBETWEEN(0,10) F5 =RANDBETWEEN(0,10) G1 G2 G3 G4 G5 H1 odd H2 =SUMPRODUCT($A2:$F2,{1,0,1,0,1,0}) H3 =SUMPRODUCT($A3:$F3,{1,0,1,0,1,0}) H4 =SUMPRODUCT($A4:$F4,{1,0,1,0,1,0}) H5 =SUMPRODUCT($A5:$F5,{1,0,1,0,1,0}) I1 even I2 =SUMPRODUCT($A2:$F2,{0,1,0,1,0,1}) I3 =SUMPRODUCT($A3:$F3,{0,1,0,1,0,1}) I4 =SUMPRODUCT($A4:$F4,{0,1,0,1,0,1}) I5 =SUMPRODUCT($A5:$F5,{0,1,0,1,0,1})

Table 1 (in English, decimal-comma locale) A1 n1 A2 =RANDBETWEEN(0;10) A3 =RANDBETWEEN(0;10) A4 =RANDBETWEEN(0;10) A5 =RANDBETWEEN(0;10) B1 n2 B2 =RANDBETWEEN(0;10) B3 =RANDBETWEEN(0;10) B4 =RANDBETWEEN(0;10) B5 =RANDBETWEEN(0;10) C1 n3 C2 =RANDBETWEEN(0;10) C3 =RANDBETWEEN(0;10) C4 =RANDBETWEEN(0;10) C5 =RANDBETWEEN(0;10) D1 n4 D2 =RANDBETWEEN(0;10) D3 =RANDBETWEEN(0;10) D4 =RANDBETWEEN(0;10) D5 =RANDBETWEEN(0;10) E1 n5 E2 =RANDBETWEEN(0;10) E3 =RANDBETWEEN(0;10) E4 =RANDBETWEEN(0;10) E5 =RANDBETWEEN(0;10) F1 n6 F2 =RANDBETWEEN(0;10) F3 =RANDBETWEEN(0;10) F4 =RANDBETWEEN(0;10) F5 =RANDBETWEEN(0;10) G1 G2 G3 G4 G5 H1 odd H2 =SUMPRODUCT($A2:$F2;{1;0;1;0;1;0}) H3 =SUMPRODUCT($A3:$F3;{1;0;1;0;1;0}) H4 =SUMPRODUCT($A4:$F4;{1;0;1;0;1;0}) H5 =SUMPRODUCT($A5:$F5;{1;0;1;0;1;0}) I1 even I2 =SUMPRODUCT($A2:$F2;{0;1;0;1;0;1}) I3 =SUMPRODUCT($A3:$F3;{0;1;0;1;0;1}) I4 =SUMPRODUCT($A4:$F4;{0;1;0;1;0;1}) I5 =SUMPRODUCT($A5:$F5;{0;1;0;1;0;1})

* Table is bulit with Numbers v2.

Regards,

H

Reply

Answer 12

Jun 1, 2015 8:48 AM in response to Hiroto

Hiroto,

Good suggestion. The reason I did not do that is because it does not update automatically if you resize the table. The convenience of your solution, however may outweigh the utility of the solution I proposed.

Reply

Answer 13

Jun 1, 2015 9:18 AM in response to Hiroto

Hello Hiroto, this is a near-perfect solution, thank you for your contribution. My only trouble is that when I use your equation:

=SUMPRODUCT(B3:F3;{0;1;0;1;0})

It does not work. I am using Numbers v3.2.2 and I receive the error: "all arguments to SUMPRODUCT must be the same length".

I wonder if there is an alternate syntax or perhaps the newer versions of numbers ditched array support, as Wayne suggests?

Any help here would be massively appreciated as this solution is so close to what I need! 🙂

PS. I am using the European currency punctuation and I presume this is why I must use semicolons instead of commas. Perhaps this changes the curly brackets to something else?

EDIT - Just discovered this in the documentation... perhaps this version of Numbers does not support anything other than a collection of cells in the SUMPRODUCTS function?

EDIT AGAIN - It gets weirder, as this works:

SUMPRODUCT({1;2};{3;4})

Which means that the constant arrays work. If I replace the {1;2} above with a range of two cells it no longer works:

SUMPRODUCT(B3:C3;{3;4})

That returns the error about length.

Totally bizarre. I'm boggled.

Reply

Answer 14

Jun 1, 2015 9:07 AM in response to Yellowbox

Hey Ian,

Big thanks to you and Wayne for contributing here but unfortunately I may not be explaining myself enough: I do not want to modify any tables or add any rows, etc. I am doing global budgeting for a company that has more than 15 offices and 250 employees and the amount of tables and sheets and linking that is required is intense. Having to add in rows and tables (even if they are hidden) is not going to work out nicely and if I had to go to such a work-around I would just ditch Numbers (sadly) and move over to Excel where the simple equation is readily available (though this too creates work; rebuilding all the existing sheets from scratch).

The reason that Hiroto's solutions makes the most sense is because this is annual financing: the tables will never grow horizontally as they are either listing yearly quarters or months of the year (finite values).

Hope that helps explain and my apologies if I wasn't clear enough before.

Once again big thank you for your suggestions and time put into helping me out.

And lastly, if either of you know about the syntax or error written above please let me know!

Cheers all.

Reply

Answer 15

SGIII

Level 8

35,336 points

Jun 1, 2015 10:30 AM in response to Dylan Cromwell

Dylan Cromwell wrote:

And lastly, if either of you know about the syntax or error written above please let me know!

Have been "following along at home" on this and can't reproduce a syntax error:

Perhaps your problem is related to your localization settings?? (I use , whereas you use ; it it's possible there is an exotic bug in there somewhere).

I am doing global budgeting for a company that has more than 15 offices and 250 employees and the amount of tables and sheets and linking that is required is intense.

Numbers is great for personal projects and many business uses too, but I don't think it is engineered to handle situations such as you describe. Excel would be my tool of choice for that.

SG

Reply