Functions

nkosikhona.msani89271

Hi There,

I trying to Calculate the total number of Change Request Opened in Each Year as of 2006,through 2020.

There a formula I can use for this?

Paul Newcome

Your formula would look something along the lines of:

=COUNTIFS([Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = ####)

In place of #### you would enter the year number or cell reference if you already have the years listed out.

Andrée Starå

Hi,

Do you want to check it on the same sheet or multiple sheets?

Can you describe your process in more detailand maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

nkosikhona.msani89271

It from One Sheet.

I have a column that contains a date the Change Request was submitted. So What I want is to calculate number of Changes Submitted, let say for an example: Total Number of Change Requests submitted in year 2006.

Paul Newcome

Your formula would look something along the lines of:

=COUNTIFS([Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = ####)

In place of #### you would enter the year number or cell reference if you already have the years listed out.

Andrée Starå

@nkosikhona.msani89271

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

nkosikhona.msani89271

@Paul Newcome, Thank you it worked perfect.

@Andrée Staråthank you Yes one more think I am trying to create the approval from, from different departments, so what happens if we have an urgent change request it needs to be approved by 4 different department before it can be implemented. My question is: is it possible to have one cell that will contain the decision of them all (Approved or Declined) but if once of them declines it then it stays declined.

Andrée Starå

@nkosikhona.msani89271

Happy to help!

Yes, it's possible.

Try something like this.

=IF(CONTAINS("Declined"; [Approver 1]@row:[Approver 4]@row); "Declined")

The same version but with the below changes for your and others convenience.

=IF(CONTAINS("Declined", [Approver 1]@row:[Approver 4]@row), "Declined")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

nkosikhona.msani89271

Hi Andree, when I send one Approval Request to multiple contact (as per snip shot below), will it wait for all of them to approve the request or if one of them approves it will mark it as approved?

Andrée Starå

@nkosikhona.msani89271

It's only one that can Approve or Decline, and then the Approval Request will not be valid anymore. If you want all four to answer separately, they will have to be added to four different columns.