Column CountIF Totals won't plot on Chart Widget when summarized

Brian Sergio

The Goal:

I built a Dashboard Chart Widget to present Summary Data and found it works when I pull from a Column Total that uses a Count formula that is then Totaled with a simple addition formula.

=COUNT([High School]3:[High School]124)

=[High School]2 + [High School]125 + [High School]172 + [High School]212 + [High School]303 + [High School]320 + [High School]362 + [High School]473 + [High School]502 + [High School]513 + [High School]522

The Problem:

When I reference a Column Total using the "Count IF Children" formula to Count Column Checkboxes that are Checked, the Values referenced won't plot on the Chart Widget.

=COUNTIF(CHILDREN([AT&T Event Invite]2, [AT&T Event Invite]125, [AT&T Event Invite]172, [AT&T Event Invite]212, [AT&T Event Invite]303, [AT&T Event Invite]320, [AT&T Event Invite]362, [AT&T Event Invite]473, [AT&T Event Invite]502, [AT&T Event Invite]513, [AT&T Event Invite]522), 1) + ""

What am I missing?   Is there an alternative approach?

Paul Newcome

 + ""

That converts a numeric value into a text value. Text values can't be pulled for charts. If you are putting the formula in a text/number type column, you can remove that form the end and generate a numeric value which can be pulled into the chart data.

Brian Sergio

The Row/Column Parent formula works fine as is, but when I remove the +"" that I get a #BOOLEAN EXPECTED error message.

What should the formula look like?

Paul Newcome

You have provided two different screenshots. My post was in reference to your first one. It doesn't look like the formulas in your first screenshot are being placed in a checkbox column.

Brian Sergio

What you see in the first Screenshot is the Summary Sheet with the reference values that are pulling from the multiple columns.

The Problem Description is in reference to the Checkbox Columns being reference.

Sorry for the confusion!

Paul Newcome

What formula is populating the numbers for the chart data?

Brian Sergio

It's not a formula but a reference link to the other sheet's Column Grand Total which uses the formula in the 1st screenshot above. Formula is listed under The Problem in Red.

Paul Newcome

Ah. Ok. I understand now. So you have the formula that ends in + "" on sheet one in a checkbox column and you are creating a direct link on sheet 2 to this data.

The problem is that you are generating a text string by finishing your formula in + "" which is what you need to use to get the number in the checkbox type column. So when you pull the data over to sheet 2, you are pulling a text value which of course cannot be used to generate data for a chart.

You are going to need to convert this text to numbers either on the source sheet or on the target sheet.

Since you are creating individual cell links anyway, it may be easiest to do this on the target sheet.

Start the function

=VALUE(

then create a cross sheet reference to the appropriate cell and close it out.

=VALUE({Other Sheet Range 1})

You are going to have to do this for each cell that you want to create a link for.

Another option would be to create new text/number columns on the source sheet to replicate the data and use the VALUE function there. Then you would create your cell links to point at the data in these new columns.

Brian Sergio

Paul - We're getting closer!

When I use that formula in the Target Summary sheet, and then set the reference to the particular Column Total, it automatically changes the Totals for the other cells on the Target Summary Sheet that I already pasted into.

Paul Newcome

You don't want to "Edit Reference". You want to make each cell it's own unique reference.

Depending on the size of your sheet, it may actually be better to use additional columns on the source sheet that can be used to mimic the layout of your metrics sheet. Then on the source sheet you can use the value function without having to create so many cross sheet references.

If the additional columns match your metrics sheet layout, you can use direct cell linking and can even link in "chunks" at a time instead of having to do one cell at a time.

Brian Sergio

What formula should I use to convert the Text Boolean Checkbox Column Subtotal Rows to a Number for reference back to the Master Summary sheet.

I created a group of rows in the Source sheet to hold the converted numbers which I will then reference.

Paul Newcome

They would have to go into different COLUMNS. New rows in the same columns will present the same issues.

If using the source sheet to convert, you would add new text/number type columns and use a formula like the one in your screenshot above.

Brian Sergio

Thx Paul for all your help!

I ended up creating a new section in my Summary sheet that holds my reference data pulled from the source sheet, and then used the formula suggested convert my data to populate my Dashboard Chart Widget - Works great Now!

Paying it forward - Hope this set of posts helps others!

Paul Newcome

Happy to help. 👍️