finalizing a formula help.

Jason Jordan
Jason Jordan ✭✭✭✭
edited 06/14/22 in Add Ons and Integrations

I'm almost there.. i want to count each checkbox but i'm getting a 1 when all the boxes have a check and a zero if i remove one checkbox. I want to total the checks every month. please help.

with the below I'm getting 1 when all 6 boxes are checked and if I uncheck one the count goes to zero.

thank you in advance - Anyone? Marco?

=COUNTIFS([Closed date]:[Closed date], AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))), [CPT1 Approved]:[CPT1 Approved], 1, [CPT2 Approved]:[CPT2 Approved], 1, [CPT3 Approved]:[CPT3 Approved], 1, [CPT4 Approved]:[CPT4 Approved], 1, [CPT5 approved]:[CPT5 approved], 1, [CPT6 Approved]:[CPT6 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Your formula is counting dates that have all checkboxes checked. If you want to count checkboxes you need to count boxes in each column and sum them. Try 1 piece confirm you get the right answer before combining them. I could have misplaced a pattern:

    =COUNTIFS([CPT1 Approved]:[CPT1 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))) +

    COUNTIFS([CPT2 Approved]:[CPT2 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))) +

    COUNTIFS([CPT3 Approved]:[CPT3 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))) +

    COUNTIFS([CPT4 Approved]:[CPT4 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))) +

    COUNTIFS([CPT5 Approved]:[CPT5 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))) +

    COUNTIFS([CPT6 Approved]:[CPT6 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [Closed date]:[Closed date], AND(month(@cell)= IFERROR(MONTH(@cell), 0, IF(MONTH(TODAY()) = 1, 12, IF(MONTH(TODAY()) - 1))), year(@cell)=IFERROR(YEAR(@cell), 0, IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.