Pivot Table

[chaju]

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...

[chaju]

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...

[Colo]

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

	A	B	C	D
1	Money	25	30	=SUMIF(B1:C1,">0")
2	HK	-45	2	=SUMIF(B2:C2,">0")
3	EQ	3	-4	=SUMIF(B3:C3,">0")

[chaju]

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

[sen_edp]

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

[chaju]

any more ideas?

[Chris Davison]

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

ie :

money 10 20
money -5 70

?

[Cosmos75]

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

How did you copy what was in Excel?

[Chris Davison]

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