Is it possible to divide the result of a countif formula by another?

Tori Green

I am trying to create sheet summary data that returns the % complete we are on confirming attendees at a new event.

In a check box column I have the formula "=COUNTIF([Confirmed - New Dates]3:[Confirmed - New Dates]47, 1) +" which returns the correct value.

Then in another check box column I have the total amount of people we need to contact: "=COUNTIF(Confirmed3:Confirmed47, 1) + "" which also returns the correct value.

However, I want to divide the returned value in the first column by the second column to show what percentage complete we are. And this returns an "Invalid Operation" Error.

I've tried as Sheet Summary data: "=[Confirmed for New Dates]#/[Total Confirmed]#"

and in a cell: =[Confirmed - New Dates]1/ Confirmed1

Any suggestions on how I can show this metric? The hope is to include the metric itself on a dashboard.

Thanks!

Paul Newcome

The problem is that by adding + "" to get the numbers to appear in a checkbox column, you are actually converting them into a text value. You will need to convert these back into numerical values before you can use them to divide.

=VALUE([Confirmed - New Dates]1) / VALUE(Confirmed1)

MCorbin

The problem appears to be that you're trying to divide a check box by a number. Try this in your Sheet Summary field:

=COUNTIF([Confirmed - New Dates]3:[Confirmed - New Dates]47, 1)/COUNTIF(Confirmed3:Confirmed47, 1)

Tori Green

Thank you! The =Value function was exactly what I needed!

Paul Newcome

Happy to help. 👍️