formula help again
Hi again...
I'm looking to count checked check boxes by the person who completed them based of a date previous month.
i have 6 check box columns labled CPT1 Approved, CPT2 Approved, CPT3 Approved, CPT4 Approved, CPT5 Approved, CPT6 Approved.
One column name CPT 1-6 completed by column
One Closed date column.
I'd like to count the checked boxes based of the person in the "CPT1-6 completed by" column for the previous month. I've been successful in getting the individual counts without the other columns for example counting check marks =(COUNTIFS([CPT1 Approved]:[CPT6 Approved], 1)). counting the number of times a name shows up =COUNTIFS([CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray") but I can't figure out how to combine them to get me the count just for that person for the previous month. I have not tried any formula for the previous month yet because I can't get the above to work first. I'm doing this in a sheet summary.
I've tried several combinations of count/if/s, if and/or statements that I've found in the community but nothing seems to work please help.
Thank you in advance
Best Answer
-
The problem here is that the way you have it written, all columns must be checked for it to count. If you want to count individual columns, you will need to write out multiple COUNTIFS (one for each checkbox column) and add them all together.
=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, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
+
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()))), [CPT2 Approved]:[CPT2 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
+
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()))), [CPT3 Approved]:[CPT3 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
so on and so forth so that it looks more like this...
=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, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray") + 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()))), [CPT2 Approved]:[CPT2 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray") + 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()))), [CPT3 Approved]:[CPT3 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
Obviously this can turn into a crazy long formula. My suggestion would be to insert a column that adds up how many boxes are checked on each row and then use a SUMIFS to grab this column.
=COUNTIFS([CPT1 Approved]@row:[CPT6 Approved]@row, @cell = 1)
Then in the summary field:
=SUMIFS([Helper Column]:[Helper Column], [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()))), [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
Answers
-
Hi,
Try:
=(COUNTIFS([CPT1 Approved]:[CPT6 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [closed date]:[closed date], month(@cell)=month(today())-1)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk I'm getting #incorrect Argument Set with that formula.
-
@Mark Cronk i read about adding an IFERROR now i'm getting the unparseable error =(COUNTIFS([CPT1 Approved]:[CPT6 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", ([Closed date]:[Closed date], IFERROR(MONTH(@cell) = MONTH(TODAY()) - 1)))
I read that if the date columns are blank it messes it up and adding the IFERROR before the month it would fix it. but now I'm getting UNPARSEABLE.
-
=COUNTIFS([CPT1 Approved]:[CPT6 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray", [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()))))
I found a formula for date that @Paul Newcome created that I think I need to account for year and December. I plugged it in but I'm still getting unparseable.
-
Okay I've gotten all of them to work on their own in summary, now how do I combine them in one summary field to get the desired result for one staff member?
=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()))))
=COUNTIFS([CPT1 Approved]:[CPT6 Approved], 1)
=COUNTIFS([CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
-
Okay I'm getting there - now I've gotten it to work with the exception that my formula is returning when all six are checked instead of counting each check individually.. how can I make it so it will count each check individually.
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")
-
The problem here is that the way you have it written, all columns must be checked for it to count. If you want to count individual columns, you will need to write out multiple COUNTIFS (one for each checkbox column) and add them all together.
=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, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
+
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()))), [CPT2 Approved]:[CPT2 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
+
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()))), [CPT3 Approved]:[CPT3 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
so on and so forth so that it looks more like this...
=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, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray") + 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()))), [CPT2 Approved]:[CPT2 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray") + 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()))), [CPT3 Approved]:[CPT3 Approved], 1, [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
Obviously this can turn into a crazy long formula. My suggestion would be to insert a column that adds up how many boxes are checked on each row and then use a SUMIFS to grab this column.
=COUNTIFS([CPT1 Approved]@row:[CPT6 Approved]@row, @cell = 1)
Then in the summary field:
=SUMIFS([Helper Column]:[Helper Column], [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()))), [CPT 1-6 completed by]:[CPT 1-6 completed by], ="Christi Gray")
-
@Paul Newcome thank you! I didn't think about a helper column again.. got lost in my own head. worked perfectly!
-
Happy to help. 👍️
