Pivot/SUMIF Help

Not as easy as Pivot Table drag-and-drop but not really that hard. One formula, in B2, that you can fill right and down. Distinct category names are listed down the left. Month names across the top. I changed some of the months in your sample dataset to show

The names have to match exactly. Note that when you type in month names Numbers automatically converts them to a date-time value and the existence of the time part of that or the year part of the date can cause puzzling failures to match. So it can be safer to type a ' followed by the month name. That tells Numbers to treat the name as text.

The formula in B2, filled right and down:

=SUMIFS(Table 1::$E,Table 1::$G,$A2,Table 1::$A,B$1)

You can read more about SUMIFS and see examples here. It works with column-condition pairs.

SG

FlaParrotHead wrote:

Too bad I can not have Col A in the report automatically filled in with unique Categories.

To paste distinct categories into Column A you can use this Copy Distinct Automator Service (Dropbox download).

To install in your Numbers > Service menu just double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy.

Thereafter to use just select the range of cells from which you want to extract distinct values, choose Copy Distinct from the Numbers > Services menu, click once in the top destination cell (e.g. A2) and type command-v to paste.

SG

hi, I'v met another issue, the instruction in the first image shows it doesnt work. what is the problem? thanks a lot.

hi, I try again type more entries, and copy distinct and then command-v in the right column, but the "extract distinct values" shows, instead of the distinct values.

how does that happen?

thanks a lot.

Yanni.

hi, SGIII, your post is vey helpful. but new issue arises, the copy distinct workflow doesnt work smoothly. see my reply. zhangyanni918@qq.com

thanks a lot.

Yanni

Hi Yanni,

The steps are:

1. first select the range of cells from which you want to extract distinct values (no blank cells)
2. choose Copy Distinct from the Services Menu
3. click once in a Numbers cell
4. command-v to paste

SG

HI,SG, When I tried to install the Copy Distinct workflow on another macbook with latest version of Numbers and OS XI and the Privacy passed, the menu of Numbers ddoes not show the Copy Distinct, what is the reason.

thanks a lot. and this community does not allow to upload video. if you need the video how I installed the workflow I can send to you.zhangyanni918@qq.com

I can't get this to work at all. Some of the parameters make no sense to me. What is $A2 doing? What are the parms after $A2 doing?

I tried creating a calorie tracking sumif table based on another table that records my daily calories along with dates. Been pecking at the sumif function for hours but only get errors. I want to scream because if I buyExcel it will take 1 minute but I already own Numbers so isn't there a way to get this to work?

flashkube2 wrote:

Some of the parameters make no sense to me. What is $A2 doing? What are the parms after $A2 doing?

Sounds as if you could benefit from studying the explanation for SUMIFS, here.

SG

Ah, yes, I have been staring at that help file page since 9 this morning trying to figure it out. Sadly, the instructions there are wrong because it does not show the results of the command. The author just assumes it works. I want to know what it looks like after the result. I've tried at least 30 or 40 times with different variations trying to get past the error but no luck so far. Is there somebody who can demonstrate how to do this in a different way? The current explanation is baffling. I'm trying to do what you did by placing it in a new table from my raw data file.

flashkube2 wrote:

Ah, yes, I have been staring at that help file page since 9 this morning trying to figure it out. Sadly, the instructions there are wrong because it does not show the results of the command. The author just assumes it works.

Sorry you're having trouble. Did you study the examples in the links? The expected results are there, as is typical in Formula and Functions Help in your Help menu.

SG

Are you asking about this formula from a (much) earlier post by SGIII in this thread?

=SUMIFS(Table 1::$E,Table 1::$G,$A2,Table 1::$A,B$1)

Syntax: SUMIFS(sum-values,test-values,condition,test-values,condition)

In the formula:

sum-values: Table 1::$E --all of the values in column E of Table 1

test-values,condition Table 1::&G, $A2 --values in all of column G of table 1,
(that are equal to) the value in cell A2 of this table. (2 will increment

to match the row containing the formula. A is fixed.

test-values,condition Table 1::$A, B$1 -- values in all of column A of table 1,

(that are equal to) the value in cell B1 of this table. (B will increment

to match the column containing the formula. 1 is fixed.

Translated to English for the formula in B2 of 'this table':

calculate the SUM of all the numbers in column E of Table 1 that are on rows where the value in column G of Table 1 is the same as the value in cell A2 of 'this table' AND the value in column A of Table 1 is the same as the value in cell B1 of 'this table.'

Both conditions must return TRUE for the value on that row of column A of Table 1 to be incuded in the SUM.

Regards,

Barry

I appreciate your help. I'm just not smart enough to figure out how to do this. I have given up because I can't see any relationship between the values in the formula and the result in the examples and I cannot get past the error messages no matter how hard I try.

Thank you.

Hi Flashkube,

The discussion in my post earlier today applies to these two tables, posted near the beginning of this thread by SGIII.

The formula, shown immediately above this line, is in the selected cell, B2 of Table 2.

The cells (and ranges of cells) included in the formula are highlighted in the image with the same colours as the tokens in the formula as shown.

With the table where you can see it, you might have a better opportunity to see what is going on.

The sum in the selected cell (777.11) includes only the first three amounts in Column E, because they are the only ones on the table that satisfy both conditions of the SUMIFS formula.

--- --- -- --- --- --

If you're still stuck after doing that, I'd suggest starting a new thread with your question to eliminate the need to scroll through/bypass all the existing material in this thread, and to avoid the focus on only one possible solution.

Give a description of what you want to accomplish, without reference to the method (formula) you think is best to do the job. Add details on the input data and its form (how it's placed on the table) and the end goal. A screen shot of a sample table using real or made up data will likely be useful.

There are usually several ways of accomplishing the same task, and keeping the focus on the starting point and the goal opens the door to solutions from several sources.

Regards,

Barry