Previous 1 2 15 Replies Latest reply: Feb 7, 2014 2:06 PM by SGIII
Level 1

Can someone please help me do this. I dont even know what to call it. Conditional categorization?

I have a table with costs, and another table to separate them into categtroies as shown:

I want to be able to enter a category in the green column, and have those nunmber appear and total up in the COST TOTALS in the second table.

What formula do I put in the COST TOTALS cells?

Solved by SGIII on Feb 7, 2014 1:55 PM Solved

Is it possible to generate a pop-up menu in the  green collumn from the text entered into the Category column of the Summary table?

In Numbers 3 that's quite easy (though not fully automatic; a few clicks and keystrokes are required).  But I think it's harder in Numbers 2.  It is not possible in either version to have the the Pop-Up automatically get its values from a range of cells.

SG

#### All replies

• Level 7

Tracy,

It's a good start. Make the bottom row of each Table a Footer Row so we can put summary calculations there without it getting confused with the input data.

Also, name your tables so we can refer to them in equations. Select both tables and then in the Table Inspector, click on Table Name. I suggest calling the one on the left Entries and the one on the right Summary.

Then, in B2 of Summary, we'll enter the following equation:

=SUMIF(Entries::A, A, Entries::B)

Fill Down by dragging the fill handle in the middle of the bottom cell border. Stop just above the Footer Row where the total will be. In the Column B Footer cell, write: =SUM(B).

That shoud do it. There were lots of steps there, so ask about any you don't understand.

Jerry

• Level 6
iWork

Tracy,

Name the table on the left "Data" and the table on the right "Summary".

In the table table summary add a header row and a footer row.  In my example the header is row 1 and the footer is row 13:

For the table on the right (title "Summary") make the header row contain the titles as show.  Also enter the categories as show.  in column A of the footer enter the text "Total"

B2=SUMIF(Data::A, A2,Data::B)

this is shorthand for select cell B2 and type (or copy and paste from here) the formula:

=SUMIF(Data::A, A2,Data::B)

now select B2,  and fill down by hovering the cirsor over the bottom edge and drag the yellow circle down as needed.

In the footer row (in this example B13) enter the formula:

B13=SUM(B)

Please let us know if you have other questions

• Level 1

Awesome! Thanks All.

Now how is the best way to get this forumlla into the other cells in the summary tabel? Dragging it out doesnt work. I'm not sure which to label relative and which absolute.

.

• Level 6
iWork
now select B2,  and fill down by hovering the cursor over the bottom edge and drag the yellow circle down as needed.
• Level 1

I must be doing somethig wrong. I tried that  and this is what I get:

Oh, I'm still in Numbers 09

• Level 1

I got it now. I fiddled with the formula a bit unitl the column name showed up rathert than the range.

• Level 1

Thanks so much all fro solving my problem!

Next question on this same topic please:

My categories are often qomplex words, phrases or even sentences. Is it possible to generate a pop-up menu in the  green collumn from the text entered into the Category column of the Summary table?

• Level 6
iWork

select the cells in the column (in your example) B1 thru the end of column B, then open the cell formatter and change the format to a pop-up menu.  This will create a pop-up menu with the items you selected.

Now select one of the cells in the table you just formatted with the pop-up menu, copy

paste this into column A of the summary table and set the selection to each of the categories

• Level 6
iWork

To fill down in Numbers '09 selec the control on the botton-right corner and drag down

• Level 6
Mac OS X

Is it possible to generate a pop-up menu in the  green collumn from the text entered into the Category column of the Summary table?

In Numbers 3 that's quite easy (though not fully automatic; a few clicks and keystrokes are required).  But I think it's harder in Numbers 2.  It is not possible in either version to have the the Pop-Up automatically get its values from a range of cells.

SG

• Level 1

Sorry, but I can not get this to work. I must be mnissing something. My pop-up menus are populated with "1, 2, 3"

I want them to be populated with the items in the category column of the Summary table.

• Level 1

• Level 6
iWork

SG,

In fact if you select a range of cells with text in then and format them as a pop-up they will all be changed to a popup with a single entry for each distinct "phrase" (so long as you are using Numbers 3.x)

• Level 1

In fact if you select a range of cells with text in then and format them as a pop-up they will all be changed to a popup with a single entry for each distinct "phrase" (so long as you are using Numbers 3.x)

Thanks. unfortunate that I am in 09 and it wont do that.

Previous 1 2