SUM of total billed per client but only once, on first occurrence

[KauriV]

Hi there,

I am trying to format data for a project that has thousands of clients that are each billed separately for each product every month.

What I am trying to do is make it so that column D shows the total billed (Column B) for client (Column A) during the month/year but only in the row when the client first appears in that month. This way, the sum of Col B and Col D would be equal.

Is this possible?

Here is an example of the desired result:

Client	Amount	Month/Year	Desired Result
A	599	5/2020	848
B	499	5/2020	957
C	649	5/2020	1218
D	449	5/2020	449
A	249	5/2020
C	569	5/2020
B	458	5/2020
A	124	6/2021	587
D	555	6/2021	1187
D	632	6/2021
A	463	6/2021
B	444	6/2021	444

 

[Saba Sabaratnam]

Hi

Enter the following formula in E2 and copy it down

=IF((COUNTIFS($A$2:A2,A2,$C$2:C2,C2))=1,SUMIFS(B:B,A:A,A2,C:C,C2),"")


My spreadsheet layout is given below.

Kind regards

Saba

 

[mrshl9898]

=IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)=1,SUMIFS(B:B,A:A,A2,C:C,C2),"")

 

[KauriV]

Thank you!!!

 

[alansidman]

. If this presentation works for you, then an alternative solution is to group the data using Power Query

Book6
	A	B	C
1	Client	Date	Sum
2	A	May-2020	848
3	B	May-2020	957
4	C	May-2020	1218
5	D	May-2020	449
6	A	June-2021	587
7	D	June-2021	1187
8	B	June-2021	444
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Amount", Int64.Type}, {"Month/Year", type datetime}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"Month/Year"]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([#"Month/Year"]), type text),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Month Name", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Month/Year"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Client", "Date"}, {{"Sum", each List.Sum([Amount]), type nullable number}})
in
    #"Grouped Rows"