Trying to count multiple people in a cell

Paul Newcome

@Deanna Vandermeer While you cannot reference multiple sheets within a function, you can link multiple functions together that each reference a different sheet.

=COUNTM({Sheet A Multi-Select})

would give you the count for Sheet A

=COUNTM({Sheet B Multi-Select})

would give you the count for Sheet B

=COUNTM({Sheet A Multi-Select}) + COUNTM({Sheet B Multi-Select})

would combine the count for both.

This is the only way to reference multiple sheets within a single cell because it breaks the different sheets into separate functions.

Andrée Starå

@Paul Newcome Haha!

Yes, of course!

I just had a . . . moment:

https://media.giphy.com/media/aVtdz7iNVPI1W/giphy.gif

Paul Newcome

@Andrée Starå Haha. I have plenty of those moments.

Andrée Starå

@Paul Newcome Watch out! There's a new contender! 🤣

Deanna Vandermeer

@Andrée Starå - I will submit an enhancement requests. I have tried the sheet summary formulas but unfortunately even there I am finding an issue with my multi-contact column type formulas returning two additional counts to the total. My assumption, based from quite literally pretending to set up a filter and counting each and every choice I see in the dropdown to set a filter. What I am finding when I do this is, and why I am assuming it is returning 2 over the number it should, is because it is somehow including "Blank" and "Current User". Odd I know but I can find no other explanation why I am always getting a number that is two over each and every time. So, @Paul Newcome workaround seems to be a fix (for now). His solution for that was to simply add "-2" to the end of the formula, which I have done.

However, now I am trying to figure out a good way to either run a metrics sheets to return not only the distinct count of the person (resource) AND the total number of projects they are assigned to for each individual project sheet. I did try the COUNTM + COUNTM solution that Paul mentioned above but it is currently not returning the correct number either. So I'll just keep working at it until I come up with a workaround or you or Paul have other suggestions. You are both so very helpful and I want to thank you both for the help thus far.

Paul Newcome

@Deanna Vandermeer Happy to help!!

You said you tried COUNTM + COUNTM but it is not returning the correct results. My suggestion is to start by separating the formulas and troubleshooting each one individually.

If you also have "Blank" and "Current User" in the other sheets, it may be as simple as subtracting 2 from each, then adding those together.

=(COUNTM(......) - 2) + (COUNTM(......) - 2)

Deanna Vandermeer

Thank you I do already have my formulas written individually and they work. I always start off by doing that first. I am doing your trick of -2 but the combining just simply does not want to return a valid number at all but I'll keep getting after it until I crack this. I appreciate all of your wonderful suggestions. I did come across one weird anomaly in one of my results. Each and every multi-contact column so far is returning exactly 2 over so the -2 solution is working but I have one column that is returning exactly 10 more than it should, weird I know, but I have yet to figure this one out and am at my wits end with this. I have never experienced so much trouble with formulas. Anyway, thank you again for being so helpful.

Andrée Starå

@Deanna Vandermeer

Excellent!

Strange that it adds almost two each time. Have you reported it to the Support Team?

Smartsheet Support Team

I'm always happy to help!

Paul Newcome

@Deanna Vandermeer At this point, I would also suggest contacting support. I can understand (somewhat) the +2 from "Blank" and "Current User", but the +10... That has me a touch befuddled (unless it is counting the 10 empty rows at the bottom of the sheet, but then why wouldn't it do that on all sheets??).

Please let us know if they are able to help.

Deanna Vandermeer

@Andrée Starå & @Paul Newcome -- I have already reported this to my account customer support rep and he has reported this but I will follow up and also report via the link Andree included (thank you). Yes, Paul my first assumption was that it was counting 10 additional empty cells in that column but in reality there are empty cells in that particular column so this has be befuddled as well. I will keep you both in the loop in case I, or support, ever find out the cause. It seems to me that in addition to the wonderful COUNTM that we currently have we need a DISTINCTM too, maybe they will work on this. In the meantime, I have moved on to plan B and am working on a metric sheet and have abandoned the Sheet Summary option for these particular counts. But I sure do appreciate you both very much and will update as I learn or figure out more. Thank you both so much.

Andrée Starå

@Deanna Vandermeer

You're more than welcome!

Have a fantastic weekend!