Forum Discussion

Aprilklfg's avatar
Aprilklfg
Copper Contributor
Aug 18, 2022
Solved

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))

excel
Formulas and Functions

10 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 19, 2022

    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")))
  • sivakumarrj's avatar
    sivakumarrj
    Brass Contributor
    Aug 18, 2022

    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.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 18, 2022

    Hi Aprilklfg 

    That should be:

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

Resources