Excel Subtotal without calculate duplicate lines

S

Siew Lee

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.webp
 
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?
 
Hi Becky,
If I filter "Calvin", the spend = $200 to elimate duplication. If unfiltered, total spend = $1270.
 
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.
 
Back
Top