Adding values in a column

Carolyn Wildermuth

I'm trying to sum the values in this column, but the formulas I have tried aren't working. The values are linked in from another sheet. This is one formula I've tried: =SUM(At Risk Count1:At Risk Count89) but it's unparseable. Can anyone help? Thanks.

Paul Newcome

The problem is the + "" at the end. It is converting it to a text value I assume because you have it in a checkbox or flag column.

Create another column on the sheet you have the sum formula on and use

=VALUE([At Risk Count]@row)

as a column formula. Then you can sum this new column.

Antonio Figueroa

It should look like this =sum([At Risk Count]1:[At Risk Count]98)

Carolyn Wildermuth

Thank you.

That's closer, but it doesn't seem to be recognizing the values as numbers. The correct sum value should be 2, but it's coming up as 0.

Paul Newcome

I see the numbers are coming in through cell links or formulas with cross sheet references. How exactly are the numbers being populate? It could be that they are being populated onto the sheet as text values.

Carolyn Wildermuth

The numbers are calculated in the original sheet using COUNTIF into the original sheet summary. Then, they are linked into the second sheet from the summary of the original sheet.

Paul Newcome

What is the COUNTIF formula exactly?

Carolyn Wildermuth

This is the formula I'm using in the original project sheet: =VALUE(COUNTIF([At Risk]:[At Risk], 1)) + ""

It calculates correctly in the original sheet and carries the correct value to the master sheet I've created.

Paul Newcome

The problem is the + "" at the end. It is converting it to a text value I assume because you have it in a checkbox or flag column.

Create another column on the sheet you have the sum formula on and use

=VALUE([At Risk Count]@row)

as a column formula. Then you can sum this new column.

Carolyn Wildermuth

Thanks, Paul. That did the trick! Much appreciated.

Paul Newcome

Happy to help. 👍️