Create a total count of item selected from a pop up in a cell in Numbers

Pleley,

I would absolutely go with your idea of having a separate Sheet for each month, and then having a Summary Sheet and Table. I'm not saying that SG's suggestion is wrong because it's a matter of personal preference and experience with advanced features. I prefer smaller chunks of data and I prefer sticking with basic functions even when advanced features may have benefits. I'm short on expertise with Pivot Tables; I've played with them, but I always return to what I am more comfortable with and know how to maintain. My hat's off to SG, this is not criticism.

Here's an example using your format.

The expression in B2 of the Summary Table is: =COUNTIF(January::Table 1::D, $A2)

In the February column you would substitute February for January in that expression. You could grab the month name from the Header, but you're only doing this once, so keep it simple.

Jerry

If you've broken it down into different sheets (tabs) then you will have difficulty extracting summary data that your manager probably wants to see. It is almost always more efficient to keep your data in one table and use COUNTIFS or SUMIFS (or better yet, Pivot Tables, which don't require you to write formulas) to summarize by cremation/traditional, etc.

For a simple example, let's say your data looks something like this.

To summarize it you can quickly do things like this:

Or this:

Or this (for counts instead of sums, click the circled i):

To get started click in the table with the data and from the menu choose Organize > Create Pivot Table > On Current sheet.

Then it's just a matter of dragging the fields down into boxes until you get the summary you need.

No formulas to enter and debug! This took me less than 5 minutes.

Intro to pivot tables in Numbers on Mac - Apple Support

As you update the data in the table in the future you just click in the Pivot Table and in the menu go to Organize > Refresh Pivot Table to update it to include your new numbers.

SG

pleley4 wrote:

Thank you. Sounds like I need to make one huge input sheet, and have the table break it out for me? Is there a service that I could contract to make these spreadsheets up for me?

It's really as simple as copy-pasting the data you have in the separate sheets for each month into one sheet. If you don't have a date column already in each table then you would want to add that first and make sure it is populated with the appropriate month before copy-pasting.

This part (putting the data all in one table) is probably at most 10-20 minutes of work, a one-time task that almost anyone can do because it's just select, command-c, click once in a cell of the target table, command-v.

Once the data is together in the table there's another 5 minutes of work to create Pivot Tables as shown in the example. You'll drag your Service Type field into the boxes where I've dragged Type.

That approach is MUCH easier than trying to use formulas to consolidate data scattered across individual month tables on separate sheets.

And once you've got the data together remember you can use not just Pivot Tables but also Filters it to get views identical to what you would now have in each separate table on separate monthly sheets.

SG

COUNTIF is what you need.

See description here: COUNTIF - Apple Support

jerry

Still suggest you give the Pivot Table approach a try. It requires less expertise than inputing and debugging formulas, and is less prone to error. You literally just click and drag. It's much less work for this kind of problem, especially when you have monthly data, because a Pivot Table can aggregate by month (or another period) automatically.

The Pivot Table has been a mainstream feature in spreadsheets (Excel) since the early 1990s. Numbers old-timers may be less familiar with them because Numbers only added them in the last five years. They're easy to use (probably a factor in Apple's decision to add them to Numbers). But there is a lot of engineering to do behind the scenes to make them that easy to use. Kudos to Apple for getting them to work so well on both the Mac and the iPad.

SG

pleley4,

No, I tested with Pop-ups and it's not that. Having zero as a result means that the COUNTIF function is looking in the wrong place.

Sorry, I changed the January table name to "Sales", please excuse me, so the expression in B2 changed to:

=COUNTIF(Sales::D, $A2) in this screen shot:

Please double-click in your B2 cell as I did , make a screen shot, and post it here. We can troubleshoot from there.

To make a screen shot; Type Shift-Command-4 and drag a selection around the area of interest. A PDF of that area will be produced. Type Command-C to copy the screen shot and Command-V to put it in your post here.

I think the defaults in screen capture will work with that procedure. If not let me know.