I installed the 4.1.1 Numbers update today and now double-clicking a cell does not display the formula in a popup for editing. Anybody else notice this? The keyboard shortcut is ^=, which is not too handy.
iMac with Retina 5K display, Other OS, Mac OS X 10.12.2 - not in OS menu
I can double click a cell that already contains a formula and edit the formula:
I cannot repeat your reported problem. In this case I suggest waiting Numbers and restarting. I also suggest restarting the computer.
If the problem still persists, I suggest trying a new document and see if this happens there. If it does not then it is possible the problem occurs in a document that is corrupt in some way. Try copying the data from that document into a new document.
I also have no trouble to bring up the formula editor by double-clicking the cell with formula already set.
As alternative, you can try typing '=' while holding down control key. ( control - '=' ) for the cell with formula.
# On menu, 'Edit'>'Edit cell as formula'
Be aware. This brings up the formula editor regardless of the cell contents. ( they are converted to formula )
In your simplified example, here is how you could use SUMIFS:
In B2, filled down, is:
=SUMIFS(Table A::B,Table A::A,A2)
One thing you need to be carefully about when using dates is that date cells actually contain date-time strings, even if they are formatted to only show date. And they contain a year even if they are formatted only to show month and day. So dates that look the same as they are displayed may actually be different under the surface so SUMIFS doesn't give you the result expected. To minimize that problem I simply enter dates as text, usually by just typing an apostrophe ( ' ) first and then the date.
SG
Having trouble following the numbers in your example (e.g., where does the 19 in the Summary table come from, since you have both 19 and 22 for that date in the Entries table?).
However, if you intend to sum by date, then recommend using the SUMIF or SUMIFS function. That way you won't need to edit the formula each time you change the date.
For more on SUMIF and SUMIFS see here and here.
SG
Thanks but that wipes out the existing formula. I have a spreadsheet where the cell range for a SUM on a few cells has to be updated every day. I used to be able to double-click the cell, get the popup, and click on the range in the popup, which would give it handles on the sheet. I guess I can use the menu. I wonder why they took the double-click cell edit away. It was so handy and it has not been assigned to any other action. It just doesn't do anything now.
Double-click on a cell with an existing formula or = for a cell without a formula should bring up the Formula Editor. Maybe you should try closing and relaunching Numbers.
Also, I'm wondering why you have to continually update a range within a formula. Usually that can be easily avoided with judicious use of Footer Rows and summary tables and having formulas refer to entire columns within tables. That way the formulas automatically "expand" when you add new data. Recommend having a look at the templates at File> New in your menu for good examples of this.
This is a dorky, but very useful to me, daily diet spreadsheet. So I have one table where the first column is the date and then the food or exercise item. The number of entries in a day varies. Like this:
Entries
4/25/17 | 19 | Elliptical | 0 | 0 | 0 | -120 | 20 | minute |
4/25/17 | 22 | Salmon | 0 | 24 | 48 | 464 | 8 | ounce |
Then there is a summary table for each day, tied to graphs on another page. Each cell for that day is tied to an entry on a graph on another page.
Summary
4/25/17 | 19 | 153 | 93 | 1700 | *** |
So in the above 19 is a sum of the carbs for all the entries for 4/25 in the first table. For 4/26 I just copy that cell for 4/25, change the range manually, then copy that across to the other 3 cells, which automagically reflect the new range even though they are summing different columns.
What I would love to do is this, but I don't think it's possible in Numbers.
For instance for cell B in the Summary Table
if (Summary Table: date in cell in column a) = (Entry Table: date in cell in column a) include (Entry Table: value in cell in column d) in SUM range.
I know I could include summary lines in the Entries table per day but I don't want the Entries table to have anything but the raw data.
-------------
I restarted Numbers, also created a new spreadsheet and tried it, in case my existing one was borked. Nope, when I double click a cell with or without a formula nothing happens. I may have to restart and see if that fixes it. Good to know it DOES work. Meanwhile Ctrl= for the win.
I looked at the SUMIFS originally and I couldn't make it work b/c I couldn't figure out how to make the date in the condition relative rather than hardcoded. For the Summary cell that says 7 the condition for the SUMIF should be Table A Date = current row, Date column but I could only get it to work with = '12/1/2017'
This is a simplified version of what I want to do:
Table A
Date Value
12/1 3
12/1 4
12/2 1
12/2 5
12/2 4
Summary
Date Value
12/1 7 -----> find just the matching dates in Table A, in this case the entries for 12/1, and sum them
12/2 10
Maybe I gave up too soon. Thanks for the idea.
Oh my gosh! My dates purposely don't have the time part in the date format for the date column in both tables, so your condition of just pointing to the hard coded date cell for the condition works! If I copy the same formula to the next day, (the cell that has 10 in it in the example), the A2 reference updates to A3 by itself.
Thank you for wading through my description and showing me the perfect solution.
With a cell selected just typing = should bring up the Formula Editor.
SG
to add a formula to a cell, select the cell, then type "="
In this case I suggest waiting Numbers and restarting.
should have been:
In this case I suggest quitting Numbers and restarting.
Numbers 4.1.1 edit cell formula
