SOLVED

# COUNTIFS, counting date range and word

Occasional Contributor

# COUNTIFS, counting date range and word

Hello

I am trying to create a formula to count the occurrences of a particular month and if an adjacent cell contains Yes

Say column A2:A284 are dates dd/mm/yyyy and column R2:R284 contains either Yes or No

For the month August count cells that contain Yes

I've tried the SUMPRODUCT(1*(MONTH(\$A\$2:\$A\$284)='Data Validation'!\$C13))

10 Replies

# Re: COUNTIFS, counting date range and word

That should be:

``=SUMPRODUCT(--(MONTH(\$A\$2:\$A\$284) ='Data Validation'!\$C13), --(\$R\$2:\$R\$284="yes"))``

# Re: COUNTIFS, counting date range and word

thanks - this gives a result of #value!
any other suggestions?

# Re: COUNTIFS, counting date range and word

@Aprilklfg You may try-

``=SUMPRODUCT(MONTH(A2:A7=E1)*(B2:B7="Yes"))``

best response confirmed by Aprilklfg (Occasional Contributor)
Solution

# Re: COUNTIFS, counting date range and word

RE. this gives a result of #value! any other suggestions? NO, this works no problem (sample attached):

# Re: COUNTIFS, counting date range and word

Simply try this for countifs,
=COUNTIFS(\$B\$1:\$B\$10,C1,\$A\$1:\$A\$10,">=12/07/2022")
12-07-2022 Yes Yes 7
13-07-2022 Yes
14-07-2022 No
15-07-2022 Yes
16-07-2022 No
17-07-2022 Yes
18-07-2022 Yes
19-07-2022 No
20-07-2022 Yes
21-07-2022 Yes
=COUNTIFS(\$B\$1:\$B\$10,C1,\$A\$1:\$A\$10,">=12/07/2022")
B Column count for Yes
A Column count for equal and greater than 12/07/2022 (Date can be changed according to your needs)

You can able to add multiple conditions for countifs for the same range.

# Re: COUNTIFS, counting date range and word

@L z.   Brilliant!! I appreciate your help

Thank you!

# Re: COUNTIFS, counting date range and word

Thanks, appreciate the help

# Re: COUNTIFS, counting date range and word

Glad we could help & Thanks for providing feedack

# Re: COUNTIFS, counting date range and word

BTW @Aprilklfg

If by any chance you run Excel 2021 or 365, an alternative:

``=COUNTA(FILTER(\$R\$2:\$R\$284, (MONTH(\$A\$2:\$A\$284) ='Data Validation'!\$C13) * (\$R\$2:\$R\$284="yes")))``

or

``=ROWS(FILTER(\$R\$2:\$R\$284, (MONTH(\$A\$2:\$A\$284) ='Data Validation'!\$C13) * (\$R\$2:\$R\$284="yes")))``