How do I create a report for charting without pulling in the primary column?

carben27

I have a sheet that includes many years of publications for my department. In trying to report the data to put into a chart in a dashboard, the report keeps pulling the primary column from the sheet. The primary column includes the title of the publication, and makes the chart unusable. Smartsheet says you can choose the data for the chart, but mine doesn't let me. It pulls everything, and I can't highlight only what I want. I only need the count of published publications by year. I tried using a formula in another grid sheet, but the formula didn't work. Can anyone help?

Paul Newcome

When referencing a report, the only way to determine which columns are used in the chart are by limiting the columns in the report, but... You can't get rid of the Primary column in the report. You can only hide it.

If all you need is a count on a separate sheet, you can use

=COUNTIFS({Main Sheet Date Column}, IFERROR(YEAR(@cell), 0) = 2021)

carben27

Thanks for the quick response Paul. What formula do I use for a "Type" = publication by "status" = published by year? - Carrie

Paul Newcome

If I understand you question correctly, try something like this...

=COUNTIFS({Main Sheet Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Main Sheet Status Column}, "Specific Status")

carben27

Hi Paul, The sheet name is R&D Presentations and Publications. The column titles from which I want to pull are: Type, Status, Year. There are some blank cells that haven't been filled in under each column. Each of the columns are dropdowns. We have data from 2015 - 2021. I'm using the R&D sheet as a reference sheet, trying to pull the data into its own sheet, and am doing something wrong because it always comes back unparceable. Thanks, Carrie

Paul Newcome

How are you creating the cross sheet references?

carben27

I was entering the countifs, then the box pops up and I chose reference another sheet. Even doing that, the columns I want to use are not next to each other on the reference sheet, nor does it allow me to click several columns at once - even though the instructions in smartsheet indicate that you can.

Paul Newcome

You would only select one column at a time. Whatever column it is you are wanting to reference for the very next criteria.

So in the above formula where it says {Main Sheet Date Column}, you would only select the date column that you want to evaluate.

carben27

This is what I ended up with, and it isn't working. I'm clearly doing something incorrectly.

=COUNTIFS({R&D Presentations and Publications Range 1}), IFERROR(Type(@cell), 0 = Publication, {R&D Presentations and Publications Range 2}, IFERROR(status(@cell), 0) = Published, {R&D Presentations and Publications Range 3}, IFERROR(year(@cell), 0 = 2015))

Paul Newcome

Try this variation:

=COUNTIFS({R&D Presentations and Publications Range 1}), @cell = "Publication", {R&D Presentations and Publications Range 2}, @cell = "Published", {R&D Presentations and Publications Range 3}, IFERROR(YEAR(@cell), 0) = 2015)

carben27

Paul, it still didn't work. Even when I try to use a formula inside the smartsheet where the data is housed, nothing seems to work. I'm not sure what to do. Thanks for your help.

Paul Newcome

Are you getting an error or an incorrect count?

carben27

It says #unparceable.

Paul Newcome

My apologies. When I was tweaking your original formula, I missed a parenthesis that needed removed.

=COUNTIFS({R&D Presentations and Publications Range 1}, @cell = "Publication", {R&D Presentations and Publications Range 2}, @cell = "Published", {R&D Presentations and Publications Range 3}, IFERROR(YEAR(@cell), 0) = 2015)

carben27

Thanks Paul, that worked in getting a calculation. Now it's giving me an incorrect count. It came back with a 0, but there should be 5.

Paul Newcome

Try creating a filter on the sheet that has the same criteria and see how many rows come up.