How can I calculate the total days of a column when another column meets a criterion?

Try this:

=VALUE(SUM([Approval Waiting Time]:[Approval Waiting Time]))

All comments

Instead of a column for Today's Date what you need is a column for Date Approved. Then an automation to record a date into that column when the status changes to Approved. Then you do NETDAYS between the Date Initiated and Date Approved.

Also, without a date in the Date Approved column you're going to get an error with the NETDAYS function. So you wrap that around in an IFERROR that just leaves the field blank when there's an error due to no date yet in the Date Approved column.

Hi @Mike TV

Thank you for your help.

I settled up the automation and it worked however I am having an error in the NETDAYS column.

I checked and both columns' properties are dates. When I try to add dates manually the formula works. Do you how can I fix that?

Thank you!

The column your NETDAYS formula is in should be text/number. If it's a date column then that could be what is causing the problem. The other 2 columns the Date Approved and Date Initiated should be date columns.

I was missing one of the dates in a column and the formula could not work due to that. Now it's working!

Thank you!

Hi @Mike TV

The net days' column is working now but I can't make the sum formula in my Summary sheet work.

This is the formula I am using: =SUM([Approval Waiting Time]:[Approval Waiting Time])

However, it says #INVALID DATA TYPE. Do you know how can I fix that?

Try this:

=VALUE(SUM([Approval Waiting Time]:[Approval Waiting Time]))

@Mike TV

Thank you for your help.

It still shows me #INVALID DATA TYPE. I think it's because some rows don't have any data and the formula shows as #INVALID DATA TYPE.

I believe you need to add to the formula in the Approval Waiting Time column so the errors go away from it.

You can add the IFERROR to the beginning of your formula and the result for an error would be double quotes to make it leave the cell blank if there'd be an error. After doing that, this other formula might clear up.