Counting data in Rows Formula

[soul1974]

Hi,
Could someone please assist me with a formula? I need to count the number of items that have a certain colour in the first cell and data in another cell.
I need to count how many T1, T2, T3, T4, T5, B1, B2, B3, B4 and B5's are there in the different colour rows.

 

[vletm]

Something like this ...
... without Your expected results.

 

[soul1974]

Hi, Thanks for the post.

Not exactly what I am trying to do. I am truly lost

I have to calculate how many T and B's have serial numbers in each individual colour.

For example it would say

Colour	T	B
Purple	20	50
Blue	12	20
	2	30

 

[vletm]

soul1974​

If You're still lost ...
... Could You explain well ...
# Your original ... was per T1... B5 and Your above is per Serial?
... those are different or how?
# Your above has max 100 serials, but Your example result has 134 ... and You newer data has 70 serials.
... How did You solve Your example?

 

[AlanSidman]

Power Query cannot distinguish colors, but I was able to count the number of T1, T2 etc by the value in Column A. Will that work for you.

here is the Mcode to do that

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column4"}),
    #"Filled Down" = Table.FillDown(#"Removed Other Columns",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1", "Column4"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[soul1974]

soul1974​

If You're still lost ...
... Could You explain well ...
# Your original ... was per T1... B5 and Your above is per Serial?
... those are different or how?
# Your above has max 100 serials, but Your example result has 134 ... and You newer data has 70 serials.
... How did You solve Your example?

Hi, it's not resolved. I just changed it manually, for example.

 

[soul1974]

Power Query cannot distinguish colors, but I was able to count the number of T1, T2 etc by the value in Column A. Will that work for you.

here is the Mcode to do that

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "Column4"}),
    #"Filled Down" = Table.FillDown(#"Removed Other Columns",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1", "Column4"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Thank you for taking the time to try to assist me, but this solution does not work for me.

 

[vletm]

soul1974​

Your I just changed it manually, for example.
If You're showing something for example then even those should be realistic.
Are those realistic / correct / verified / expected result based Your file?

 

[soul1974]

soul1974​

Your I just changed it manually, for example.
If You're showing something for example then even those should be realistic.
Are those realistic / correct / verified / expected result based Your file?

Hi, it does work for me. Thank you for the assistance.

My spreadsheet has 190 individual items divided into different colour groups; each item consists of 10 parts (5 tops and 5 bottoms) that have serial numbers. when a part is faulty, I have to replace it. I have to calculate how many tops or bottoms are in for repair of the colour groups and indicate it (Purple - 10 tops and 30 bottoms, for example)

 

[vletm]

soul1974​

I asked / wrote: Are those realistic / correct / verified / expected result based Your file?
You wrote: Hi, it does work for me. Thank you for the assistance.
You mean that Your file's example which has 70 serials gives totally 134 ... for You, it works...
I would use something like this to get 70 from 70 serials.

 

[soul1974]

soul1974​

I asked / wrote: Are those realistic / correct / verified / expected result based Your file?
You wrote: Hi, it does work for me. Thank you for the assistance.
You mean that Your file's example which has 70 serials gives totally 134 ... for You, it works...
I would use something like this to get 70 from 70 serials.

Hi @vletm, that is amazing. Thank you so much.