# ExcelSubtotal without calculate duplicate lines

#### Siew Lee

Hi all excel expert,
Need help as above mentioned in my subject.
Example I have data below pulled from system which consist of duplication lines with same spend (Shaded in yellow). Any excel formula can help to sum the "weekly spend" by only count once the duplication lines which will return a result of \$ 1270.00. But I need a formula of subtotal which the sum can be show result with filter by "Name".

#### Becky

##### Webmistress
Welcome to the forum

Just so I understand, you want a formula that will give you the total weekly spend for a particular name? For example 'Calvin' = \$400?

#### Siew Lee

Hi Becky,
If I filter "Calvin", the spend = \$200 to elimate duplication. If unfiltered, total spend = \$1270.

#### Becky

##### Webmistress
So you want to delete the duplicates?

#### Siew Lee

Hi Becky,
Nope. Remain duplication lines but when calculate with this formula, those "Name" column duplication lines will excluded for calculation.
Assumed data range is A2:B11,I tried =SUMPRODUCT(B2:B11/COUNTIF(A2:A11,A2:A11)) and it works but this formula cannot cater the subtotal features and sum value counted included hidden line when I filter by "Name". The result I wish is when I filtered "Calvin", spend = \$200, unfiltered, spend = \$1270.00.

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.