Count Distinct Collect Find not calculating correctly
I have a formula, =COUNT(DISTINCT(COLLECT({CW Number}, {Opportunity Type}, FIND(Category@row, @cell) > 0))), which is not calculating correctly. See image below. For the Volunteer Type of Tutoring it lists 8 but it should be 9. All other calculations using this formula calculate at 1 or 0. The totals should be much higher. The totals are calculated from a sheet which receives information from a Smartsheet form the Volunteers complete on a regular basis.
The purpose of the formula is to count the number of volunteers who have provided a specific volunteer service. It is not to count the number of times the specific volunteer service.The categories which are used in this formula are explained below.
"CW Number" is the unique number issued to the volunteer and a way to identify the volunteer. Each volunteer enters their CW Number when completing the form.
"Opportunity Type" is the different Volunteer categories (Clerical, Driver, etc.) which the volunteers indicate they were active in for the time period they are entering into the form. The volunteer selects the category from a drop down list. They can select more than one category. So that is why the Find function is being used.
In addition, I would like to add $Category@row, {Year}, =$Year$2 to the formula to calculate the totals for a specific year. {Year} is the year a volunteer type was performed for. This is a "helper" column which has been added to the sheet the Smartsheet form feeds into. The helper column calculates the year based on the Report End Date the volunteer indicates when completing the form. $Year$2 is the specific cell on the metrics sheet which indicates the year the metrics sheet is developed for. A dashboard is developed form the metrics sheet.
Any help would greatly be appreciated.
Best Answers
-
The formula would only produce a count o f1 for each since 001943 is different from 1943.
You can account for this by adjusting the formulas in the helper columns to add zeros so that all are of the same length using a nested IF. Here's the theory behind it (assuming 6 digits but can be adjusted for more or less)...
If the number is less than 10 (only 1 digit long), then we need to append 5 zeros. If the number is less than 100 (only 2 digits long), then we need to append 4 zeros. If the number is less than 1,000 (only 3 digits long) then we need to append 3 zeros. So on and so forth.
=IF(VALUE([Number Column]@row)< 10, "00000", IF(VALUE([Number Column]@row)< 100, "0000", IF(VALUE([Number Column]@row)< 1000, "000", .............................) + "" + VALUE([Number Column]@row)
-
The nested IF needs to be closed out before adding the "value".
=IF(........, IF(........, IF(........))) + "" + VALUE(........)
Answers
-
Are you able to provide a screenshot of the source data that shows multiple rows that should be included in a count? Columns can be temporarily hidden or blocked out, but at the very least the CW and Category columns would be very helpful to see.
Also try switching over to a HAS function instead of the FIND function. The HAS function was specifically designed for evaluating multi-select columns.
-
Thank you @Paul Newcome
This is a screen shot per your request. I checked and there are not hidden columns.
I changed the formula from FIND to HAS but now it is calculating 0 so I might not have a ( or ) in the wrong place. Below is a screen shot of the modification.
Something I just noticed in looking at the CW Number is some numbers are appearing as text with a ' appearing before any number which starts with 0. This might be throwing things off but should not cause the calculations to be 0 or 1. Several months ago I had changed the form to allow only numbers. Below is a screenshot of the form settings for CW Number.
I opened the form entered a number that is currently displayed on the sheet with '00####. When I entered the number, it would not allow me to use the ' at the beginning. I submitted and checked the sheet. The number was entered without leading zeros. How can it be that since I set the form CW Number setting to Number only that zeros are able to be entered? Could it be those individuals are using an old link to the prior form which allowed leading zeros?
Thank you for all the help you provide.
Paul
-
You have to have the same data type in every cell. Leading zeros make it a text string whereas no leading zeros are stored as numerical values.
Create a helper column in the source sheet with this in it:
=[CW Number]@row + ""
This should convert everything into text strings and would be the column you want to count in place of the CW Number column.
-
Thank you @Paul Newcome.
I added the helper column and set the formula reference to the helper column. Unfortunately, all formula totals are 1.
In the helper column (Number) all CW Number's appear as text (left alignment). Any other suggestions for the formula of what I might not have correct?
I used the form from a mobile device. Again, I added two leading zeros and those zeros were entered onto the sheet and not removed. My concern is an individual might complete the form one time using a laptop and the zeros will be removed. Then another time they might use their mobile device and the zeros will be added. Even with the helper columns converting them all to text will Smartsheet view the two numbers as being identical numbers. If only these two numbers appeared on the sheet (001943 and 1943) would the formula produce a count of 1 or 2?
Thank you.
-
The formula would only produce a count o f1 for each since 001943 is different from 1943.
You can account for this by adjusting the formulas in the helper columns to add zeros so that all are of the same length using a nested IF. Here's the theory behind it (assuming 6 digits but can be adjusted for more or less)...
If the number is less than 10 (only 1 digit long), then we need to append 5 zeros. If the number is less than 100 (only 2 digits long), then we need to append 4 zeros. If the number is less than 1,000 (only 3 digits long) then we need to append 3 zeros. So on and so forth.
=IF(VALUE([Number Column]@row)< 10, "00000", IF(VALUE([Number Column]@row)< 100, "0000", IF(VALUE([Number Column]@row)< 1000, "000", .............................) + "" + VALUE([Number Column]@row)
-
Thank you @Paul Newcome
Sorry to say I am still struggling with this formula.
The CW Number needs to be 7 digits long. Below is a screenshot of the column formula and as you can see above the formula is not producing the correct results.
I have tried the suggested formula in two ways:
- I placed it in the Number column and had it pull from the CW Number column.
- I added the CW_Number_Text column and placed the formula =[CW Number]@row + "" in the CW_Number_Text column. Then placed the most recent suggested formula in the Number column.
In both cases it produced the same results as shown in the formula below. I initially copied and paste the formula from your response and developed the remainder of the formula as needed. I also removed the " " and typed those back in.
Again, I set the form to accept numbers give people were not entering the correct number of zeros. Or there would be a letter mixed in. But as I mentioned before using the form from a mobile app (phone) the form inputs any leading zeros into this sheet.
Other thoughts, suggestions, or corrects you see I need to make?
-
The nested IF needs to be closed out before adding the "value".
=IF(........, IF(........, IF(........))) + "" + VALUE(........)
-
Thank you @Paul Newcome.
The formula works correctly with accurate counts.
Thank you for all your patience and responses.
Paul
Help Article Resources
Categories
Check out the Formula Handbook template!