Sum unique values as per price index

[H.V.K]

Hi all,

The attached file shows 3 data ranges: price index, revenue and brand. I am unable to work out a working formula to sum unique brands as per price index

To demonstrate, it will look like this when a working formula is in place



Please kindly advise,

Much appreciated,

Leo,

 

[Eloise T]

Is the spreadsheet segment above correct? i.e. There are 14 brands with a price index of less than 150,000 totaling 44,091,108?

 

[David Evans]

What version of Excel are you using? Excel 2013 can count unique items in a Pivot Table using the Distinct Count feature ...

BTW, you have blanks in your brand column - I used the filter to exclude them to arrive at your answer of 14 for "<150,000"

 

[bosco_yip]

Count unique brands as per price index :

=SUMPRODUCT((A$2:A$1311=$E2)*(C$2:C$1311<>"")*(MATCH(A$2:A$1311&C$2:C$1311,A$2:A$1311&C$2:C$1311,0)=ROW($1:$1310)))

Regards
Bosco

 

[David Evans]

What version of Excel are you using? Excel 2013 can count unique items in a Pivot Table using the Distinct Count feature ...

BTW, you have blanks in your brand column - I used the filter to exclude them to arrive at your answer of 14 for "<150,000"

Try again

 

[H.V.K]

Thanks all for your reply

@David Evans : thanks for showing a new feature of pivot table. However, the nature of my work requires as less of pivot as possible

@bosco_yip : your formula works perfectly on a simplified data table. I copied to the original file (attached below) and it didn't work. Could you please have a look into it? Thanks a lot

Much appreciated,

Leo,

 

[bosco_yip]

@bosco_yip : your formula works perfectly on a simplified data table. I copied to the original file (attached below) and it didn't work. Could you please have a look into it? Thanks a lot
Leo,

Try,

1] Your data range E$2:E$1311, but the row should be --> Row(1:1310)

So……

2] In cell E23, formula copy down :

=SUMPRODUCT((Revenue_by_product_201602011732!Q$2:Q$1311=$B23)*(Revenue_by_product_201602011732!E$2:E$1311<>"")*(MATCH(Revenue_by_product_201602011732!Q$2:Q$1311&Revenue_by_product_201602011732!E$2:E$1311,Revenue_by_product_201602011732!Q$2:Q$1311&Revenue_by_product_201602011732!E$2:E$1311,0)=ROW($1:$1310)))

Regards
Bosco

 

[H.V.K]

Amazing!

Thanks @bosco_yip for your help

Cheers,

Leo