Count the number of projects per customer

KarineMul22_TPg

Hi,

This might be very basic, but I cannot figure t out.

I have a long sheet showing among other things:

but I can have 100 lines with the same Project number for the same customer. then a couple of lines for another project for the same customer etc.

How do I count the number of different projects for 1 customer (and not the number of lines with that project number) please

KarineMul22_TPg

I have created a datasheet:

and was hoping to count from the "Project Showing" column, tried =COUNTM(Project No @row) but it counts only 1 (as you can see).

Anybody has any idea please? It has to be a sheet and not a report as it will feed into an already existing report showing customers/Project No/Line value/Status/Number of lines(tasks)

Thank you

MichaelTCA

Hello @KarineMul22_TPg

The COUNTM() function should work to count values within a cell.

=SUBSTITUTE([Projects]@row, ",", CHAR(10)) -> Enter into a dropdown column and allow multiple values per cell. This will break the values into individual objects.

Then use the COUNTM function to reference the dropdown column instead of the projects column.

KarineMul22_TPg

Hi Michael,

Not sure if I got this correctly:

I've added a dropdown column with multiple value onto my datasheet (not the source one) with the formula =SUBSTITUTE([Projects showing]@row, ",", CHAR(10))

Then used the COUNTM function, still coming back with 1 :(

So to recap:

Column 1 shows the Customers names

Column 2 "Projects Showing" (drop Down Multiple) =JOIN(COLLECT({Spares Tracker LIVE Range 2}, {Spares Tracker LIVE Range 1}, Customer@row), " - ")

Column 3 "Substitute Column" (Text/Number as too many for drop down multiple) =SUBSTITUTE([Projects showing]@row, ",", CHAR(10))

Column 4 "Counted Projects" (Text/Number)=COUNTM([Substitute Column]@row)

Thank you