Summary Counts

Options


With the above chart, I want to do a summary data that I could possible chart.

You have the Project Lead, Then I want a count by Project Type for each lead.

So looking for something like this.

Project Lead Compliance Security Project, etc

Jose Garcia 10 5 4

How would I get this done

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/19/23 Answer ✓
    Options

    Hi @Buster Davis

    I can see that you have two {cross sheet references}. Have you checked that both are looking at the correct columns in the other sheet?

    To check this, click on {Project Lead}, then Edit Reference and make sure you've clicked on the correct column name in the other sheet.

    If when you click on it, the helper pop-up only says "Reference Another Sheet" this is because the {Project Lead} is only recognized as text and is not associated with another sheet.

    You'll need to delete {the reference} and click Reference Another Sheet to make the pop-up window show where you can then select the correct sheet/column.



    However if the blue hyperlink below says Edit Reference, then when you click on it a pop-up window will appear allowing to you select the correct columns.


    Does that make sense?

    If you still need help, please provide the following:

    • Confirm what error you're getting
    • Screen capture of the {Type} or {Data2} reference pop-up window showing the column
    • Screen capture of the {Project Lead} reference pop-up window showing the column
    • The formula that's being used in the other sheet in either of the two columns, or if no formulas, the column-type for each column and how the data is input (e.g. a Form, manually in the sheet, and so on).

    Cheers!

    Genevieve

  • Buster Davis
    Buster Davis ✭✭✭
    Answer ✓
    Options

    First off, Genevieve, Thank you for taking a look at this one. Your time on as well as others was very much appreciated. I was missing the second reference. Once I created it and did some manual verifications, It seem to work so I extended it to the other columns and spotted check some numbers manually. I am not the greatest when it comes to formulas as math has not been my strong set in this area. This will help a lot for my project portfolio management dashboard I am crafting. Thank you again



  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Buster Davis

    I'm so glad to hear you were able to get it working, well-done! 🙂

«1

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @Buster Davis

    Would suggest the addition of a helper sheet to run those numbers, and based on the parameters you set, the metric / helper sheet will auto update. I've attached a version of this that I run for our market research projects, along with how the formula is set up to get you started. Where I have the Category Labels, I would suggest you putting your team members names, and then in the sections below you can create labels of any desired datapoint. This information makes things easily chartable by person, or across larger categories.



    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Buster Davis
    Options

    not sure if I follow your process.

    I want to be able to count by person the types of projects they are working on then chart that

    I can't seem to follow doing that in your answer

  • Buster Davis
    Options

    I have the following helper sheet setup, but starting with Jose on the formula, I am getting unparsable




  • Razetto
    Razetto ✭✭✭✭✭✭
    edited 06/07/23
    Options

    @Buster Davis I think your formula is not referencing the project lead column that's in the other sheet.

    =countifs(collect({lead type},{type},$type@row,{project lead},{catergory1}$1))

  • Buster Davis
    Options


    So lets take a step back on this one because the above formula is not working.

    The data sheet layout is below: it shows the two columns.


    Here is the helper sheet and here is the formula

    =countifs(collect{Lead Type}({type},$type@row,{project lead},{catergory1}$1)) and it is giving me the parse error


    In this sheet, it has a reference to the data sheet:

    and this reference is pointing to the two columns in master data sheet:

    So going back to the helper sheet - Project Lead Category, I want to take the lead, and do a count of project by type they are currently working on.


    Tell me what I am doing wrong or need corrected.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Just try a basic COUNTIFS. No need for a COUNT/COLLECT or COUNTIFS/COLLECT.

    =COUNTIFS({Type}, $Type@row, {Project Lead}, [Catergory1]$1)

  • Buster Davis
    Options

    Paul, i just copied your formula and pasted it on the sheet and still the same output.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 06/08/23
    Options

    Hey @Buster Davis

    Just hopping in here to clarify! You won't be able to copy/paste {references} unless you've already built them out in your sheet with that exact name.

    I'd recommend re-writing Paul's exact formula in your sheet directly, ensuring that you're creating the {cross sheet references} pointing at the correct columns. Note that Paul's formula uses two separate references, one to each column. (Whereas in your screen captures you have one reference across two columns).

    Let us know if that helped!

  • Buster Davis
    Options

    It's just the formula that i copied and paste. I had done this before with no issues. Under manage references, I did create the link. could it be something else?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Buster Davis

    Did you try writing it out in your sheet instead of copy/pasting? This will ensure Column Names are spelled correctly as well. For example, Paul's formula says [Catergory1]$1 but it looks like your sheet is [Category 1] with a space.

    If this hasn't helped, can you post a screen capture with the formula open in the cell?

  • Buster Davis
    Options


    Thank you,

    I did find category spelled wrong and I corrected that.. I believe we are getting closer now as now it my cell message is saying invalid ref.

    Not sure how that can be as I have the reference created above



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/08/23
    Options

    Try retyping the formula from scratch and using the steps outlined below to create the cross sheet references as you type out the formula.



  • Buster Davis
    Options

    any further help on this one.. still needing some assistance

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Buster Davis

    It sounds like you may have typed the words {Type} into your formula (or copy/pasted them into your sheet) instead of selecting a column to reference on your second sheet while building the formula - could that be correct?

    If so, here's another Community post where I explained how to create {these} references, step-by-step. This, along with Paul's article linked above, should help you build your formula.

    If that still hasn't helped - do you have any formulas in your {Type} column in the other sheet or the {Project Lead} column in that other sheet?

  • Buster Davis
    Options

    Thank you,

    this is what I have for the formula currently. Thoughts



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!