Formula to count the number of checked boxes in a column per person
Afternoon,
I am trying to create a formula to count the number of checked boxes in one column per a person listed in a separate column. (So I would run the same formula for each person, with just switching out the person's name.) I continue to get either an "incorrect argument error" or an "unparseable" error for any change that I make. Would someone know of the correct formula to use?
Thank you!
Best Answer
-
Hi Rebecca,
Are you using Cross Sheet References (building this in a separate sheet) or are you using the formula in the same sheet?
Column name references [in these] are for your same sheet, and column references {in these} are cross-sheet references.
Same Sheet:
=COUNTIFS([L2/L3 Handling Request]:[L2/L3 Handling Request], "Their Name", [QA Completed Same Day]:[QA Completed Same Day], 1)
(Here's a Help Center article on how to reference columns in the same sheet)
or Cross Sheet:
=COUNTIFS({L2 Sup Call KPI's Raw Data Range 2}, "Their Name", {L2 Sup Call KPI Raw Data Range 5}, 1)
(Here's a Help Center article on cross sheet references)
Let me know if either of these have worked for you!
Cheers,
Genevieve
Answers
-
Hi Rebecca,
It sounds like you would just need a COUNTIFS formula (with an S at the end because you're looking for multiple criteria). You would want to have this in a third column for your calculations.
Try something like this:
=COUNTIFS([Name Column]:[Name Column], "Their Name", [Checkbox Column]:[Checkbox Column], 1)
You'd have to swap out the column names for your own. If this doesn't work, it would be helpful to see a copy/paste of the current formula you're using, along with screen captures of your sheet (but please block out any sensitive data).
Cheers,
Genevieve
-
Hi Genevieve!
I had tried with a countifs formula, not sure what I am doing wrong. With my data columns and per your suggestion, I am using (still getting an imparseable error):
=COUNTIFS([L2 Sup Call KPI's Raw Data Range 2]:[L2 Sup Call KPI Raw Data Range 2], "Name", [L2 Sup Call KPI Raw Data Range 5]:[L2 Sup Call KPI's Raw Data Range 5], 1)
Thanks for your help!
-
Hi Rebecca,
Are you using Cross Sheet References (building this in a separate sheet) or are you using the formula in the same sheet?
Column name references [in these] are for your same sheet, and column references {in these} are cross-sheet references.
Same Sheet:
=COUNTIFS([L2/L3 Handling Request]:[L2/L3 Handling Request], "Their Name", [QA Completed Same Day]:[QA Completed Same Day], 1)
(Here's a Help Center article on how to reference columns in the same sheet)
or Cross Sheet:
=COUNTIFS({L2 Sup Call KPI's Raw Data Range 2}, "Their Name", {L2 Sup Call KPI Raw Data Range 5}, 1)
(Here's a Help Center article on cross sheet references)
Let me know if either of these have worked for you!
Cheers,
Genevieve
-
Glad it worked for you!
-
Thanks so much for your help!
-
Genevieve,
Do you think you can help with a percentage calculation as well? Trying to get the percentage of "Same Day Completed QA's" vs. the "Total Number of Sup Calls Completed."
-
Hi Rebecca,
No problem!
Make sure that the column is set to a Percentage type of column (click on the column name to highlight the whole thing, then choose the % symbol from the top menu bar).
Then try this:
=[Same Day Completed QA's]@row/[Total Number of Sup Calls Completed]@row
Then you can drag-fill this down that whole percentage column. The @row after the column name [in these] means that where you put the formula, the formula will only look at the values in that row.
Here are some Help Center articles you may find useful:
-
I am receiving an Unparseable error when I use that formula. Thank you for working with me, I am still learning this program!
-
Could you post a screen capture, like above, but showing your formula?
OH! I just noticed that the blue row is likely not your column names, but is just text in a cell (is that correct?) You'll need to use the column names.
To automatically input the correct column name, after you have double clicked on a cell to type in it, put the = sign, then click on the cell in that column you want to reference.
This will bring in the right name [in these] along with the row number right after, like so:
[Primary Column]1
Change out the number at the end (1) to be @row:
[Primary Column]@row
Does that make sense?
-
That was the problem! Thank you!!!
-
Wonderful! Happy to help 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!