PIVOT %

[Sunny FL]

I am stuck... I have tons of data as summarized below. I want to get 4 rows
for each customer. One Row will be the Sum of the NonUS, Next Row, Sum of
US, and then the next 2 rows % of the Revenue from US and NonUS for that
Customer.

I can get the first 2, it is the last 2 that is messing me up. Any ideas?

Cust# COUNTRY DATE REV
10756V NON-US 200807 10
10756V NON-US 200808 20
10756V NON-US 200801 10
10756V US 200709 20

 

[zhang]

what's up?

 

[ShaneDevenshire]

Hi,

#1: =SUMIF(B2:B5,"Non-us",D25)
#2: =SUMIF(B2:B5,"us",D25)
#3: =#1/SUM(D25)
#4: =1-#3

This assumes you data is in the range B25. suppose #1 above is in cell G1
and G2 contains #2, then in G3 you could also use
=G1/SUM(D25)
and in G4
=1-G3

 

[Sunny FL]

Thanks for the advice, but i don't think that will work given the number of
customers I have (100s). I was wondering how to do it in a PIVOT table. It
seems logical to use the % of in the format, but I dont' know how to use the
BASE FIELDS etc.