Pivot Table

chaju

Board Regular
Joined
Mar 24, 2002
Messages
82
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...
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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...
 
Upvote 0
Hi chaju!
Do you surely use pivot table? Simple formula will show the answer to you.

<TABLE bgcolor="#FFFFFF" Border="1">

<TR><TD bgcolor="#DFDED0" Align=Center >
</TD><TD bgcolor="#DFDED0" Align=Center >A</TD><TD bgcolor="#DFDED0" Align=Center >B</TD><TD bgcolor="#DFDED0" Align=Center >C</TD><TD bgcolor="#DFDED0" Align=Center >D</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >1</TD><TD Align=Right >Money </TD><TD Align=Right >25</TD><TD Align=Right >30</TD><TD Align=Right >=SUMIF(B1:C1,">0")</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >2</TD><TD Align=Right >HK </TD><TD Align=Right >-45</TD><TD Align=Right >2</TD><TD Align=Right >=SUMIF(B2:C2,">0")</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >3</TD><TD Align=Right >EQ </TD><TD Align=Right >3</TD><TD Align=Right >-4</TD><TD Align=Right >=SUMIF(B3:C3,">0")</TD></TR>

</TABLE>
 
Upvote 0
I have simplify the data,, there are actually lots of rows of data..
Using pivot table is a must
 
Upvote 0
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
 
Upvote 0
On 2002-04-08 20:17, Colo wrote:
Hi chaju!
Do you surely use pivot table? Simple formula will show the answer to you.

<TABLE bgcolor="#FFFFFF" Border="1">

<TR><TD bgcolor="#DFDED0" Align=Center >
</TD><TD bgcolor="#DFDED0" Align=Center >A</TD><TD bgcolor="#DFDED0" Align=Center >B</TD><TD bgcolor="#DFDED0" Align=Center >C</TD><TD bgcolor="#DFDED0" Align=Center >D</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >1</TD><TD Align=Right >Money </TD><TD Align=Right >25</TD><TD Align=Right >30</TD><TD Align=Right >=SUMIF(B1:C1,">0")</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >2</TD><TD Align=Right >HK </TD><TD Align=Right >-45</TD><TD Align=Right >2</TD><TD Align=Right >=SUMIF(B2:C2,">0")</TD></TR>

<TR><TD bgcolor="#DFDED0" Align=Right >3</TD><TD Align=Right >EQ </TD><TD Align=Right >3</TD><TD Align=Right >-4</TD><TD Align=Right >=SUMIF(B3:C3,">0")</TD></TR>

</TABLE>

How did you copy what was in Excel?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top