Count the number of projects per customer

KarineMul22_TPg
edited 09/20/23 in Formulas and Functions

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

Answers

  • 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
    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.

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!