Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
30th June 2006
Location
Netherlands
Posts
68

## Market share formula

Hi,
How can i formulate the following in a formula:
Sales of product A (product A belongs to product class ABC) divided by the total sales of all products in product class ABC

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Market share formula

=Sales of product A/Total sales of all products in product class ABC

3. ## Re: Market share formula

use the SUMPRODUCT function

=SUMPRODUCT(((A7:A13)="A")*(B7:B13))/SUM(B7:B13)

where Product names are in Col A and sales in Col B

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
30th June 2006
Location
Netherlands
Posts
68

## Re: Market share formula

Thanks Dave!
I don't know how the calculate the total sales for a product class. Let me attach an little file. This formula has to be valid for every record.
Thanks again.

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Market share formula

use this

=SUMPRODUCT(((A2:A7)="A")*((B2:B7)="abc")*(C2:C7))/SUM(C2:C7)

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
30th June 2006
Location
Netherlands
Posts
68

## Re: Market share formula

This formula is certainly one in the good direction but if you watch ht efile closely you will find out that product a has a market share of 10/(10+34) of the total abc market. That let's say the market share of product a in the abc market is around 22%. When i execute your formula i only got 4%. So something is missing in this formula.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Market share formula

=SUMPRODUCT(((A2:A7)="A")*((B2:B7)="abc")*(C2:C7))/SUMPRODUCT(((B2:B7)="abc")*(C2:C7))

Excel Video Tutorials / Excel Dashboards Reports

8. Member
Join Date
30th June 2006
Location
Netherlands
Posts
68

## Re: Market share formula

Thanks again!

The formula works wonderful! But it is only valid for product A and product class abc. Is there a way to copy this formula down to all records. My file contains thousands of products and hundreds of product classes and i don't want to rewrite this formula for every record.

My question is to find a formula that calculates the share of a product's sale to the total sales of that product class.

I hope someone knows!

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Market share formula

In the formula you have already got, you just need to substitute 'A' for the required product and 'ABC' for the product class required...alternatively you can have these values in a cell so
if in D1 you have the product and D2 you have the product class, the formula would change to
VB:
```=SUMPRODUCT(((A2:A7)=D1)*((B2:B7)=D2)*(C2:C7))/SUMPRODUCT(((B2:B7)=D2)*(C2:C7))

```
Hope this helps...

10. Member
Join Date
30th June 2006
Location
Netherlands
Posts
68

## Re: Market share formula

Hi,
Thanks DaveR! Your solution worked perfectly. Now i want to extend this formula with another variable: period. Now i want to calculate the share of a product'sales to the total sales of the product class sales in a particular period. I put the period variable in Column A and modified the formula in the following way:

=SUMPRODUCT(((A\$2:A\$25)=A2)*(B\$2:B\$25)=B2)*((C\$2:C\$25)=C2)*(D\$2:D\$25))/SUMPRODUCT(((C\$2:C\$25)=C2)*(D\$2:D\$25))

Unfortunately this doesn't work. I can't understand why it doesn't work.
Kees

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno