FlaParrotHead

Q: Pivot/SUMIF Help

OK -- Rather then asking too many individual questions let me give an example for the Numbers experts (I am coming from many years of Excel)

 

Month

Date

Description

Original Description

Amount

Transaction Type

Category

Account Name

April

April 13, 2016

Delta

DELTA AIR

$725.14

debit

Air Travel

Visa1

April

April 11, 2016

Parking

PARKING

$46.97

debit

Air Travel

Visa2

April

April 11, 2016

Southwest Airlines

SOUTHWESTAIR

$5.00

debit

Air Travel

Visa1

April

April 19, 2016

DR D

DR D

$160.00

debit

Doctor

CREDIT CARD

April

April 19, 2016

Electric Company

Electric Company Bill Payment

$378.89

debit

Electric

CHECKING

April

April 11, 2016

Amazon

AMAZON MKTPLACE

$15.66

debit

Electronics

Discover

April

April 1, 2016

New Seasons

NEW SEASONS MARKET

$125.47

debit

Food & Dining

visa1

April

April 21, 2016

Publix

PUBLIX

$145.23

debit

Groceries

Visa2

April

April 13, 2016

Wal-Mart

Wal-Mart

$47.77

debit

Groceries

CHECKING

April

April 11, 2016

Apple

APPLE

$497.65

debit

Hobbies

Visa1

April

April 15, 2016

Vacations

VACATIONS

$775.47

debit

Hotel

Visa1

April

April 12, 2016

Mark

PAYPAL

$100.00

debit

Hotel

CHECKING

April

April 8, 2016

Verizon

VERIZON

$115.55

debit

Phone, Internet, TV

CHECKING

April

April 19, 2016

Marchellos

MARCHELLOS

$38.87

debit

Pizza Delivery

CREDIT CARD

April

April 8, 2016

Tri-Met

TRIMET

$2.50

debit

Rental Car & Taxi

Visa2

April

April 4, 2016

Rainbow Market

RAINBOW MARKET

$21.25

debit

Travel Groceries

Visa2

April

April 1, 2016

R O C

R.O.C.C. Bill

$67.40

debit

Water

JOINT CHECKING

April

April 2, 2016

Amazon

AMZ

$138.44

debit

Woot Wine

Discover

 

 

Imagine the above sheet with many more lines, up to 12 months (obviously) and many more Categories.   I am looking to build reports, by month, by category, with subtotals for Amount at the Category and the Month Level.  Hopefully a report that I'll easily be able to also create charts/graphs from.

 

In the Excel days, this is all very easy using Pivot tables --- not such the case with Numbers but I'd like to learn from example and use Numbers if possible.

iMac, OS X El Capitan (10.11.2)

Posted on Apr 22, 2016 11:59 AM

Close

Q: Pivot/SUMIF Help

  • All replies
  • Helpful answers

Page 1 Next
  • by SGIII,Apple recommended

    SGIII SGIII Apr 22, 2016 3:22 PM in response to FlaParrotHead
    Level 6 (10,627 points)
    Mac OS X
    Apr 22, 2016 3:22 PM in response to FlaParrotHead

    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.

     

    Screen Shot 2016-04-22 at 5.10.35 PM.png

     

    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

  • by FlaParrotHead,

    FlaParrotHead FlaParrotHead Apr 22, 2016 3:25 PM in response to SGIII
    Level 1 (5 points)
    Wireless
    Apr 22, 2016 3:25 PM in response to SGIII

    SG.  This did it (with some modifications for multiple sheets).  Too bad I can not have Col A in the report automatically  filled in with unique Categories.  The month note definitely helped fix a problem that I would have needed a hammer for. 

     

    Now to spend some time understanding SUMIFS in more detail, thanks for the link (again).

  • by SGIII,Helpful

    SGIII SGIII Apr 26, 2016 12:45 PM in response to FlaParrotHead
    Level 6 (10,627 points)
    Mac OS X
    Apr 26, 2016 12:45 PM in response to FlaParrotHead

    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

  • by tulip918,

    tulip918 tulip918 Jun 19, 2016 6:42 PM in response to FlaParrotHead
    Level 1 (16 points)
    iWork
    Jun 19, 2016 6:42 PM in response to FlaParrotHead

    copydistinct-nonblank.pngcopydistinct-menu.png

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

  • by tulip918,

    tulip918 tulip918 Jun 19, 2016 6:47 PM in response to FlaParrotHead
    Level 1 (16 points)
    iWork
    Jun 19, 2016 6:47 PM in response to FlaParrotHead

    copydistinct-extract.png

    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.

  • by tulip918,

    tulip918 tulip918 Jun 19, 2016 6:53 PM in response to SGIII
    Level 1 (16 points)
    iWork
    Jun 19, 2016 6:53 PM in response to SGIII

    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

  • by SGIII,

    SGIII SGIII Jun 19, 2016 7:31 PM in response to tulip918
    Level 6 (10,627 points)
    Mac OS X
    Jun 19, 2016 7:31 PM in response to tulip918

    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

  • by tulip918,

    tulip918 tulip918 Jun 20, 2016 4:48 PM in response to SGIII
    Level 1 (16 points)
    iWork
    Jun 20, 2016 4:48 PM in response to SGIII

    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.Screen Shot 2016-06-20 at 10.47.47 PM.pngScreen Shot 2016-06-20 at 10.48.09 PM.pngScreen Shot 2016-06-20 at 10.48.21 PM.pngScreen Shot 2016-06-20 at 10.49.06 PM.pngScreen Shot 2016-06-20 at 10.50.03 PM.pngScreen Shot 2016-06-21 at 7.38.15 AM.png

    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

  • by flashkube2,

    flashkube2 flashkube2 Jul 11, 2016 8:41 AM in response to SGIII
    Level 1 (11 points)
    Mac OS X
    Jul 11, 2016 8:41 AM in response to SGIII

    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?

  • by SGIII,

    SGIII SGIII Jul 11, 2016 8:54 AM in response to flashkube2
    Level 6 (10,627 points)
    Mac OS X
    Jul 11, 2016 8:54 AM in response to flashkube2

    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

  • by flashkube2,

    flashkube2 flashkube2 Jul 11, 2016 11:07 AM in response to FlaParrotHead
    Level 1 (11 points)
    Mac OS X
    Jul 11, 2016 11:07 AM in response to FlaParrotHead

    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.

  • by SGIII,

    SGIII SGIII Jul 11, 2016 11:55 AM in response to flashkube2
    Level 6 (10,627 points)
    Mac OS X
    Jul 11, 2016 11:55 AM in response to flashkube2

    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

  • by Barry,

    Barry Barry Jul 11, 2016 12:06 PM in response to flashkube2
    Level 7 (32,271 points)
    Jul 11, 2016 12:06 PM in response to flashkube2

    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

  • by flashkube2,

    flashkube2 flashkube2 Jul 11, 2016 4:38 PM in response to Barry
    Level 1 (11 points)
    Mac OS X
    Jul 11, 2016 4:38 PM in response to 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.

Page 1 Next