MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with simple formula/macro


Posted by Dano on May 26, 2001 6:46 AM


A B C D
__________________________________________________
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

There is probably a very simple answer to this, But I'd still like to ask the experts here. In column D I would like a formula or macro that would look at column A for same location numbers and add the total revenue for the items in column C, then give a percent of total revenue that item earned for that location. Any help would be greatly appreciated!

Dano


Posted by Aladin Akyurek on May 26, 2001 7:37 AM

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

Posted by Dax on May 26, 2001 7:39 AM

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

Posted by Dax on May 26, 2001 7:40 AM

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

Posted by Aladin Akyurek on May 26, 2001 7:44 AM

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

when a single criterion is used for the required computation.

Aladin

Posted by Dax on May 26, 2001 8:00 AM

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.

Posted by Aladin Akyurek on May 26, 2001 8:47 AM

?

PS, this doesn't mean war!

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

Aladin