Count the number of projects per customer
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!