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 crosssheet 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 crosssheet 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 dragfill 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
Check out the Formula Handbook template!