Trying to calculate percentage but only for rows that are checked in Helper Column

julieg

I'm working on a Summary formula:

=SUM(Complete# / [Total Deliverables]#)

I would like to calculate this percentage, but only when Complete# is a row where the the following helper column is checked.

[Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1

Any Ideas? Thank you!

Dan W

So you want to Count how many Key deliverables are "Complete" in the status. Then divide that number by the amount of deliverables total? I'm assuming the Helper column becomes checked when the status column becomes "complete"?

If that is the case we can remove the helper from the equation. with

=COUNTIF(Status:Status, CONTAINS("complete", @cell)) / [Total Deliverables]#

If you need to keep the helper then

=COUNTIF([Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

If the Helper column is tied to something other than the status.

=COUNTIFS(Status:Status, "Complete", [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

Dan W

SUMIF is the tool you need!

=SUMIFS([Complete#]:[Complete#], [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / SUM([Total Deliverables]:[Total Deliverables])

SUMIF will let you add a condition such as, this box needs to be checked.

If you have more than one condition you can use SUMIFS

Here is the SS link for the full explanation.

https://help.smartsheet.com/function/sumif

julieg

Hi Dan!

Thanks so much for responding so quickly. I tried your formula above and received UNPARSABLE

I think one problem may be that the [Total Deliverables]# is a summary sheet value.

I tried =SUMIFS(Complete#, [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / SUM([Total Deliverables]#)

but I'm receiving INCORRECT ARGUMENT SET

Any ideas? Thanks so much for your help.

Dan W

=SUMIFS([Complete#]:[Complete#], [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

If total deliverables is a Sheet Summary field then this will work

julieg

Hi Dan,

thanks for the help.

=SUMIFS([Complete#]:[Complete#], [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

is returning UNPARSABLE. I apologize as i didn't make clear that "Complete#" from the original formula is not a column name, but a value in a drop down list. (all of that may have been clear to you, but wanted to be sure)

I Imagine that may be a problem?

Thanks again Dan

Dan W

Can you share a screenshot of your sheet? Removing any sensitive info.

That way I can recreate 100% what you are working with to find the correct solution.

julieg

Hi Dan,

Thanks again. Please see the screenshot below. The formula in the summary sheet that I circled is incorrect and the original formula i started with.

So as mentioned above

=SUM(Complete# / [Total Deliverables]#)

I would like to calculate this percentage, but only when Complete# (drop down value in the status column) is a row where the the following helper column is checked.

[Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1

Thanks again Dan.

Dan W

So you want to Count how many Key deliverables are "Complete" in the status. Then divide that number by the amount of deliverables total? I'm assuming the Helper column becomes checked when the status column becomes "complete"?

If that is the case we can remove the helper from the equation. with

=COUNTIF(Status:Status, CONTAINS("complete", @cell)) / [Total Deliverables]#

If you need to keep the helper then

=COUNTIF([Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

If the Helper column is tied to something other than the status.

=COUNTIFS(Status:Status, "Complete", [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

julieg

Hi Dan,

thanks again for your time.

The Helper column [Key Deliverables Helper Column]:[Key Deliverables Helper Column] has already been checked in the sheet to identify any deliverable rows. I then count the number of complete# in the status column when the Helper column is checked. Then divide by the Summary sheet value [Total Deliverables]#

I should have unhidden the Helper column in the screen shot so you could see the deliverables rows are checked.

Let me know if i should change anything based on the Helper column already being checked.

Thank you again Dan

Julie

julieg

Hi Dan,

=COUNTIFS(Status:Status, "Complete", [Key Deliverables Helper Column]:[Key Deliverables Helper Column], 1) / [Total Deliverables]#

seems to have worked perfectly!

Thank you for your patience and have a great day!

Dan W

Glad we could get this problem solved! Happy to help, have a nice day!