finalizing a formula help.

Jason Jordan

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

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