I am attempting to create a formula that counts the number of applications approved each week?

pris

I have five different sheets collecting information about 5 different applications and each sheet has a date-approved column. I want to collect the number of approved applications for each application type, each week (1-52). I wanted to create a column formula that will count the applications that are approved if the approved date column has a number representing the week number under the column Week.

I initially thought =COUNTIF([DBT Approved Date]:[DBT Approved Date], WEEKNUMBER(@cell)="[Week]"@cell)) and I would modify each one based on the source sheet but I think I am missing something because it isn't producing the data I need. Below is an example of how I would like the datasheet or report to look.

Week	DBT	EBP	FFT	MST	TF-CBT
1
2
3
4

KPH

Hi @pris

As you are using the COUNTIFS function, you can add more criteria to the function by just adding another range - criteria pair. In this case the criteria would use the YEAR function to evaluate if the YEAR of the date is 2023 or 2024. I'll paste a suggestion further down in case you want to try this for yourself before seeing my suggestion.

.

.

.

.

.

=COUNTIFS([DBT Approved Date]:[DBT Approved Date], ISDATE(@cell), [DBT Approved Date]:[DBT Approved Date], WEEKNUMBER(@cell) = Week@row, [DBT Approved Date]:[DBT Approved Date], YEAR(@cell) = 2024)

KPH

Hi again @pris

Your formula goes a little awry at the end which will make it #UNPARSEABLE. You just need Week@row not "[Week]"@cell, like this:

=COUNTIF( [DBT Approved Date]:[DBT Approved Date], WEEKNUMBER(@cell) = Week@row)

I also suggest including an ISDATE when evaluating a column for week numbers as trying to extract the WEEKNUMBER from a cell that is not a date (such as a blank cell) will introduce an #INVALID DATA TYPE error. To add this you need to switch from COUNTIF to COUNTIFS.

Try:

=COUNTIFS([DBT Approved Date]:[DBT Approved Date], ISDATE(@cell), [DBT Approved Date]:[DBT Approved Date], WEEKNUMBER(@cell) = Week@row)

Let me know how that goes.

pris

Hello @KPH,

The formula you provided worked! Thank you so much. However, as I am reviewing the data, we have a mix of dates from 2023 and 2024 and it is correctly counting the week they were approved but is there a way to separate 2023 from 2024?

KPH

Hi @pris

As you are using the COUNTIFS function, you can add more criteria to the function by just adding another range - criteria pair. In this case the criteria would use the YEAR function to evaluate if the YEAR of the date is 2023 or 2024. I'll paste a suggestion further down in case you want to try this for yourself before seeing my suggestion.

.

.

.

.

.

=COUNTIFS([DBT Approved Date]:[DBT Approved Date], ISDATE(@cell), [DBT Approved Date]:[DBT Approved Date], WEEKNUMBER(@cell) = Week@row, [DBT Approved Date]:[DBT Approved Date], YEAR(@cell) = 2024)

pris

You are amazing! That worked! Thank you so much @KPH

KPH

No problem, happy to help.