ColA | ColB |
AA1 | 1 |
AA2 | 1 |
AA1 | 2 |
AA4 | 1 |
AA5 | 1 |
AA2 | 2 |
AA7 | 1 |
AA8 | 1 |
AA1 | 3 |
AA4 | 2 |
Are you trying to count the number of items, or get a total of the values in the cells? =SUMIFS will do a better job of totaling the value of the cells.
[TABLE="width: 988"]
<colgroup><col></colgroup><tbody>[TR]
[TD]let[/TD]
[/TR]
[TR]
[TD] Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],[/TD]
[/TR]
[TR]
[TD] Index_1 = Table.Buffer(Table.AddIndexColumn(Source, "Index", 0, 1)),[/TD]
[/TR]
[TR]
[TD] Sorted_Lines = Table.Sort(Index_1,{{"ColA", Order.Ascending}, {"Index", Order.Ascending}}),[/TD]
[/TR]
[TR]
[TD] Index_2 = Table.AddIndexColumn(Sorted_Lines, "Index-2", 1, 1),[/TD]
[/TR]
[TR]
[TD] Index_3 = Table.Buffer(Table.AddIndexColumn(Index_2, "Index-3", 0, 1)),[/TD]
[/TR]
[TR]
[TD] Userdefinded_Col = Table.AddColumn(Index_3, "Count_from", each if[#"Index-3"] = 0 or [ColA] <> Index_3[ColA]{[#"Index-3"]-1} then [#"Index-2"] else null),[/TD]
[/TR]
[TR]
[TD] Fill_down = Table.FillDown(Userdefinded_Col,{"Count_from"}),[/TD]
[/TR]
[TR]
[TD] Column_B = Table.AddColumn(Fill_down, "Col-B", each [#"Index-3"] - [Count_from] + 2),[/TD]
[/TR]
[TR]
[TD] Sort_by_original_order = Table.Sort(Column_B,{{"Index", Order.Ascending}}),[/TD]
[/TR]
[TR]
[TD] Remove_all_help_columns = Table.RemoveColumns(Sort_by_original_order,{"Index", "Index-2", "Index-3", "Count_from"})[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]in[/TD]
[/TR]
[TR]
[TD] Remove_all_help_columns[/TD]
[/TR]
</tbody>[/TABLE]
Hi
I am looking for a similar to the below formula to find the count of item in each and every row in the entire column.
=COUNTIF($A$1:A2,A2)
=COUNTIF(Table1[[#Headers],[ColA]:[ColA]]:[@ColA],[@ColA])
ColA | Counter |
AA1 | 1 |
AA1 | 2 |
AA1 | 3 |
AA2 | 1 |
AA2 | 2 |
AA4 | 1 |
AA4 | 2 |
AA5 | 1 |
AA7 | 1 |
AA8 | 1 |