Help with simple formula/macro

Dano

I'll assume that your data occupy the range A1:C9.

{27891,"","";"Loc #","Item","Revenue";25656,"A",44.5;27891,"C",108.5;34198,"R",210.25;34198,"A",113.5;27891,"T",32.25;27891,"Y",104.75;25656,"D",124.25}

A1 contains a location number (the criterion that you want to use).

In D1 enter: =SUMIF(A3:A9,$A$1,C3:C9)

This gives the following figure: 245.5

In E1 enter: D3/SUM(C3:C9) [ format this cell by clicking on % on the icon bar ]

This gives the figure: 33%

I hope my interpretation of your query is on the right track.

Aladin

================== __________________________________________________ Loc # Item Revenue % of Total __________________________________________________ 25656 Item A 44.50 27891 Item C 108.50 34198 Item R 210.25 34198 Item A 113.50 27891 Item T 32.25 27891 Item Y 104.75 25656 Item D 124.25

__________________________________________________ Loc # Item Revenue % of Total __________________________________________________ 25656 Item A 44.50 27891 Item C 108.50 34198 Item R 210.25 34198 Item A 113.50 27891 Item T 32.25 27891 Item Y 104.75 25656 Item D 124.25

Hi,

There are a few ways you could do this. If you want to just use a formula then an array formula would do, something like this:-

=SUM(IF($A$2:$A$8=A2,$C$2:$C$8))/SUM($C$2:$C$8)*100

Two very important things to remember. Firstly, as this is an array formula you need to enter it by pressing Ctrl+Shift+Enter otherwise it will not work. Secondly, if the list is actually much better than this then the array formulas will slow down your worksheet calculation significantly. In that case a pivot table would be a much better way of performing the calculations you need.

HTH,
Dax.

Re: meant to say "if the list is much BIGGER than this" not "better"...

Dax -- No need for an array formula...

when a single criterion is used for the required computation.

Aladin

Re: Dax -- No need for an array formula...

when a single criterion is used for the required computation.

Hmmmm, bit keen there. Just to show that I am awake you could also use your method all in one formula without using two columns:-

=SUMIF($A$1:$A$7,A1,$C$1:$C$7)/SUM($C$1:$C$7)*100

PS, this doesn't mean war!

Regards,
Dax.

?

PS, this doesn't mean war!

No. Not in this part of the world, I'd say.

Aladin