Excel Subtotal without calculate duplicate lines

Joined
Feb 22, 2017
Messages
3
Reaction score
0
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".

upload_2017-2-22_11-26-34.png
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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?
 
Joined
Feb 22, 2017
Messages
3
Reaction score
0
Hi Becky,
If I filter "Calvin", the spend = $200 to elimate duplication. If unfiltered, total spend = $1270.
 
Joined
Feb 22, 2017
Messages
3
Reaction score
0
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.

Ask a Question

Top