Previous 1 2 Next 15 Replies Latest reply: Feb 7, 2014 2:06 PM by SGIII
Tracy E Level 1 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:

 

Screen Shot 2014-02-07 at 2.39.52 PM.png

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

Reply by Wayne Contello on Feb 7, 2014 12:56 PM Helpful

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:

 

Screen Shot 2014-02-07 at 2.51.15 PM.png

 

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

Reply by Jerrold Green1 on Feb 7, 2014 12:56 PM Helpful

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

All replies

  • Jerrold Green1 Level 7 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

  • Wayne Contello Level 6 Level 6
    expertise.iwork
    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:

     

    Screen Shot 2014-02-07 at 2.51.15 PM.png

     

    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

  • Tracy E Level 1 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.

     

    Screen Shot 2014-02-07 at 3.15.45 PM.png.

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

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

     

    Screen Shot 2014-02-07 at 3.20.10 PM.png

     

    Oh, I'm still in Numbers 09

  • Tracy E Level 1 Level 1

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

     

    Screen Shot 2014-02-07 at 3.22.31 PM.png

  • Tracy E Level 1 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?

     

    Screen Shot 2014-02-07 at 3.32.28 PM.png

  • Wayne Contello Level 6 Level 6
    expertise.iwork
    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.

     

    Screen Shot 2014-02-07 at 3.47.19 PM.png

    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

  • Wayne Contello Level 6 Level 6
    expertise.iwork
    iWork

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

  • SGIII Level 6 Level 6
    expertise.macosx
    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

  • Tracy E Level 1 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.

     

    Screen Shot 2014-02-07 at 3.54.08 PM.png

  • Tracy E Level 1 Level 1

    Thanks to all! Really helpful!

  • Wayne Contello Level 6 Level 6
    expertise.iwork
    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)

  • Tracy E Level 1 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 Next