Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Pivot Table

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Suppose

    I have in 3 columns and 3 rows:

    Money 25 30
    HK -45 2
    EQ 3 -4

    Can I use a pivot table to sum only the positive number such that the output would be :

    Money 55
    HK 2
    EQ 3

    Thanks very much, oh pls give a bit more guidance on what button to click under pivot table...

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Suppose

    I have in 3 columns and 3 rows:

    Money II 25 II 30 II
    HK II -45 II 2 II
    EQ II 3 II -4 II

    Can I use a pivot table to sum only the positive number such that the output would be :

    Money 55
    HK 2
    EQ 3

    Thanks very much, oh pls give a bit more guidance on what button to click under pivot table...

  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi chaju!
    Do you surely use pivot table? Simple formula will show the answer to you.












    ABCD
    1Money 2530=SUMIF(B1:C1,">0")
    2HK -452=SUMIF(B2:C2,">0")
    3EQ 3-4=SUMIF(B3:C3,">0")

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have simplify the data,, there are actually lots of rows of data..
    Using pivot table is a must

  5. #5
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HELLO CHAJOU
    I THINK YOU MUST WORK FIRSLY IN THE DATA SHEET LIKE THIS

    A B C D
    1 LABEL NUM1 NUM2 VALUE
    2 money -10 50 50
    3 hq 10 -30 10
    4 hq -5 45 45
    5 hq 10 -5 10
    6 ef -5 25 25
    7 ef -10 30 30
    8 ef -10 50 50
    9 money 10 -30 10

    the value column is the formula below
    =((B12>0)*B12)+((C12>0)*C12)
    AND AFTER YOU CAN CREATE EASILY THE PIVOT TABLE using only the value column.

    ANDREAS

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    any more ideas?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what do you want when "money" has more than a single row ?

    ie :

    money 10 20
    money -5 70

    ?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 20:17, Colo wrote:
    Hi chaju!
    Do you surely use pivot table? Simple formula will show the answer to you.












    ABCD
    1Money 2530=SUMIF(B1:C1,">0")
    2HK -452=SUMIF(B2:C2,">0")
    3EQ 3-4=SUMIF(B3:C3,">0")
    How did you copy what was in Excel?

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How did you copy what was in Excel?
    see Mark W's testings in the other test pages, he incudes the link to the site that this facility comes from

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •