Divide cell by total (multiple cells)

Dear forum members,

I'd like to get an entropy measure of diversification for companies im studying. All companies have different sales in different business segments and I'd like to get a value corresponding to this formula:

Entropy measure of diversification.JPG

In column A I have company identifiers (unique tickers), in Column B I have years (2002 until 2004), and in column C i have business segments with their respective sales figures.

I need to divide all segment sales by the total sales to use in my formula. The total sales is easy to get (=SUMIF(year and ticker)). The second part, dividing individual segments by the total IF its a business segment of ticker AAA and year 2002 is what I dont understand. (not every company has the same amount of segments, some have 3 some have 4 etc.

Thank you for your help!

DOW	2002	Segment1 sales
DOW	2002	Segment2 sales
DOW	2002	Segment3 sales
WMT	2002	Segment1 sales
WMT	2002	Segment2 sales
AMD	2002	Segment1 sales

A1 = company ticker
B1 = year
C1 = Segment
D1 = Sales for Segment
//these should be in two different columns

E1 =SUMIFS($D$1:$D$100, $A$1:$A$100, "="&A1, $B$1:$B$100, "="&B1)
// Total business sales for year

F1 =SUMIFS($D$1:$D$100, $A$1:$A$100, "="&A1, $B$1:$B$100, "="&B1, $C$1:$C$100, "="&C1)
// Total business & segment sales for year

G1 =F1 / E1
// Percentage of total business in segment


Like that?

Thanks, it has helped me a long way. I now have all the percentages of total in Column G.

However, I have a percentage per row and I need to accumulate these per company per year with this formula:

Entropy measure of diversification.JPG

I know the SUMIFS statement but the individual percentages also need to be multiplied by LN (1/Percentage).

Recap: I need to get one value per company per year like: (Seg1 percentage * LN (1/Seg1 percentage))+(Seg2 percentage * (LN (1/Seg2 percentage)) and so on.

Thank you in advance.

I figured it out.

Added a column saying G1*LN(1/G1) and then adding all the outcomes using SUMIFS(year,ticker).

Solved, thanks!!