Summing multiple columns in a row, conditional upon value in first column of row

I propose a three table solution:

1) Data: the table with your data

2)Total: a new table that sums each row

3) Summary: the table where you summarize the totals for like rows

I judiciously used input values that allow me to easily check the results so they are different than your original values...

For the table "Total":

A1=SUM(Data::2:2)

select cell A1 and fill down

For the table "Summary":

B1=SUMIF(Data :: $A, "="&A1, Total :: A)

select B1 and fill down (make sure you enter the category names in column A... order does not matter)

Hi Scott,

SUMIFS sums values in a single column for which several conditions are ALL true.

SUMIF sums values in a single column for which a single condition is true.

You want to sum values in several columns for which a single condition is true.

You'll need SUMIF, once for each column to be summed:

In Table 2::B2: =SUMIF(Table 1 :: $A,A2,Table 1 :: B)+SUMIF(Table 1 :: $A,A2,Table 1 :: C)+SUMIF(Table 1 :: $A,A2,Table 1 :: D)

Fill down to row 4 (further if you have more categories).

Regards,

Barry

Hi Scott,

If you can't beat them, join them. Which is to say, if Numbers SUMIF function wants ranges of the same size, why not give in?

Here's an example:

Formula in B2 of Data Table: =IF(LEN($A)>0, $A, "")

Fill Down to the rest of B and C.

Formula in B2 of Summary Table: =SUMIF(Data Table :: A:C, A, Data Table :: D:F)

Fill Down.

Regards,

Jerry

Keep in mind if you expand the number of columns in each row my formulas do NOT change

Hi Wayne,

Yours is a good solution, and I would have posted something similar if you hadn't beat me to it. 😉

Using a method similar to yours, I would do this:

Columns A and B of the Data Table are Header Columns.

In B2 of the Data Table, enter:

=SUM(2:2) and Fill Down

In B2 of the Summary Table, enter:

=SUMIF(Data Table :: A, A, Data Table :: B) and Fill Down.

Jerry

Hi Scott,

"On another note -- and I'm probably going to get laughed off the forum, here -- can someone put me out of my misery with regards to the syntax to indicate, "from this cell, to the last cell in this column."? I currently have 705 rows in my sheet, and currently have a fomula that says, "C9:C$705" which seems to work (but of course I'll be adding additional rows). In Google Spreadsheets this would be C9:C (I've also tried C9: ) but no luck in Numbers. I've searched high and low and haven't yet found an example of this (including the Numbers guide and the iWork F&F guide -- which is not to say it isn't there, just that I'm losing my mind trying to find it)."

Not supported in that form.

You can sum all of the cells in column C that are not in Header rows or Footer rows with =SUM(C)

Your sticking point there would be that Numbers supports up to 5 Header rows, which would prevent you from starting at C9 unless the non-header cells above that contained text (this would also depend on the formula using C—Sum would ignore text). The formula, if placed in column c, would have to be in a Header row.

But you could tear the top eight rows bottom 697 rows off the table to form a second table. (It appears not possible to drag the top row, or a set including the top row, out of the a single table to make two tables).

Before (=SUM(C) is in a non-header cell, so it throws a self-reference error)

After: SUM(C) is in a non-header cell, but now it's on a different table, and it's cell reference changes to:

=SUM(Table 2 :: $C)

The move to a different cell came on a thought that you might want the result immediately above the column of data.

Regards,

Barry

couldn't you use indirect() to form the proper range?

Here is my attempt to sum cells from a fixed starting cell to the end of the row (regardless of how many columns):

I left everything "split" apart so you can see the logic.

starting with the highlighted cell in the "Summary" from before...

B5=ADDRESS(5, COLUMNS(Data), 1, 1, "Data")

This is to build the valid address for the desired range

B6=FIND(":: $", B5)

This is to find the beginning of the last column name

B7=FIND("$", B5,B6+4)

This is to find the end of the last column name

B8=MID(B5, B6+4,B7-B6-4)

This puls out the column name (in my example "AD")

B9=SUM(INDIRECT("'Data' :: $E$5"&" : $"&B8&"$5"))

This sums the row 5 start at column 5 through the end of the row

everything consolidated (and simplified) in B9 looks like:

B9=SUM(INDIRECT("'Data' :: $E$5"&" : $"&MID(ADDRESS(1, COLUMNS(Data)), 2,FIND("$", ADDRESS(1, COLUMNS(Data)), 2)-2)&"$5"))

Scottedmo wrote:

Hi Wayne, Barry, and Jerrold (Jerry),

Thanks very much for the solutions, and the speed with which you posted them -- I do appreciate it.

For the time being I've implemented the Wayne and Jerry combo just above this comment. If anyone has any other ideas and feels like posting them, though, I'd love to hear/see 'em, as 1. I'm a bit surprised that Numbers has to be cajoled into doing what I'm trying to do here.

Having said that, I'm starting to warm to Numbers, if not just for the speed and it's mac-ness. The slowness and un-mac-ness, of google docs' spreadsheets was driving me crazy.

On another note -- and I'm probably going to get laughed off the forum, here -- 2. Can someone put me out of my misery with regards to the syntax to indicate, "from this cell, to the last cell in this column."? I currently have 705 rows in my sheet, and currently have a fomula that says, "C9:C$705" which seems to work (but of course I'll be adding additional rows). In Google Spreadsheets this would be C9:C (I've also tried C9: ) but no luck in Numbers. I've searched high and low and haven't yet found an example of this (including the Numbers guide and the iWork F&F guide -- which is not to say it isn't there, just that I'm losing my mind trying to find it).

Thanks again.

1. Apple and Numbers didn't invent the syntax and rules for SUMIF or SUMIFS. For reasons of compatibility, it's the same in Excel. I can't speak about Google.

2. The easiest way to Fill a formula to a long range of cells is to enter the formula into the uppermost cell and Copy that cell. Then click on the Label of the Column (the block at the top with the letter name of the column in it). That selects the entire column. Then, Command-Click any cells that you wish to exclude from the Selection. Now Paste and your Fill is complete. There are also menu commands for Fill, found under Insert.

I'm happy to hear that you are warming up to Numbers. It takes time, but after some while designing documents the Numbers way, with multiple small, dedicated, purposeful tables rather than a giant patchwork monolith, you will find that Excel layouts seem quite cludgy. It's unusual, when designing in the Numbers Way, to have a block of data that can't be addressed simply by referencing the column name, since all rows contain related data. Unrelated data, as well as descriptions and instructions will be in another table or text box.

Jerry

Scottedmo wrote:

Jerry -- Re. #1: Point taken. I didn't really have a problem with the SUMIF(s) functions -- was just surprised that there was no analogue in Numbers to Google's FILTER function. (Having said that, I am completely confounded by the fact that Numbers' SUMIF and SUMIFS functions don't use the same ordering of elements in their syntaxes. I've never used Sumif or Sumifs functions in a spreadsheet before, so I don't know if that's standard behaviour, but it seems odd to me.

Again, both functions, SUMIF and SUMIFS were invented elsewhere. Numbers just followed the same path as the earlier spreadsheet programs. Sure, it would be nice if whoever thought-up SUMIF had planned ahead, but that's our legacy. You are not forced ever to use SUMIF. SUMIFS will work just fine with only one criteria column, or more.

Jerry

Jerrold Green1 wrote:

Scottedmo wrote:

2. Can someone put me out of my misery with regards to the syntax to indicate, "from this cell, to the last cell in this column."? I currently have 705 rows in my sheet, and currently have a fomula that says, "C9:C$705" which seems to work (but of course I'll be adding additional rows). In Google Spreadsheets this would be C9:C (I've also tried C9: ) but no luck in Numbers.

I would use :

=SUM(OFFSET($C$9,0,0,ROWS(A)-9,1))

or more portable

Store in $C$1 or elsewhere the row num of the first cell to sum and use the formula:

=SUM(OFFSET($C$1,$C$1-1,0,ROWS(A)-$C$1,1))

For those which forgot this feature, OFFSET is able to return a range 😉

Yvan KOENIG (VALLAURIS, France) vendredi 6 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

Yvan,

Don't forget, it started as a SUMIF question.

Jerry

Hello

(1) The range returned by OFFSET may be used in a SUMIF formula.

(2) The OP clearly stated that the question which I answered was/is "in an other note". 😉

Yvan KOENIG (VALLAURIS, France) vendredi 6 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2