Summary Counts

Buster Davis

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

Genevieve P.

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

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.

Hi @Buster Davis

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

Colleen Patterson

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

Buster Davis

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

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

Razetto

@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

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

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

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

Buster Davis

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

Genevieve P.

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

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.

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

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

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

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Buster Davis

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

Genevieve P.

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

Thank you,

this is what I have for the formula currently. Thoughts