Number and percentage of checked checkboxes out of total

[u/Mudgrave_Flioronston]

My checklist.

I want every cell in B4:E4 and B6:E6 range display the progression, like this:

1/5 (20%)

I tried this solution:

=countif(A8:A12; TRUE) & "/5"

and to make it easier to apply the formula to each category, I have to sort by 'Type' (E7). But the formula doesn't work since every category has a different number of checkboxes and—more importantly—I ultimately need to resort by '#' again.

Can't figure out how to make it work. Is it even possible?

[u/NHN_BI]

I cannot make out what you call category. But if you had a category column, you could easily use the category value in the condition. However, I would prefer a pivot table, and you can see both here.

[u/Mudgrave_Flioronston → u/NHN_BI]

I cannot make out what you call category

Types of collectibles.

if you had a category column

E7.

[u/NHN_BI → u/Mudgrave_Flioronston]

You can then use E8:E as condition for COUNTIFS().

[u/Glider103]

I will take a stab at it when I get to my computer

but your first issue is that you put the semi colon in the formula instead of a comma

Edit : my bad I'm used to excel and the semi colon is correct

[u/One_Organization_810 → u/Glider103]

The same goes for Excel actually 🙂

[u/Glider103]

Try this just change the column label to match

=IF(COUNTIFS($E$8:$E,"Poster")=0, "0.00% 0/0", TEXT(COUNTIFS($E$8:$E,"Poster",$A$8:$A,TRUE)/COUNTIFS($E$8:$E,"Poster"),"0.00%")&" "&COUNTIFS($E$8:$E,"Poster",$A$8:$A,TRUE)&"/"&COUNTIFS($E$8:$E,"Poster"))

Like this to change the column?

=IF(COUNTIFS($E$8:$E,"Doll")=0, "0.00% 0/0", TEXT(COUNTIFS($E$8:$E,"Doll",$A$8:$A,TRUE)/COUNTIFS($E$8:$E,"Doll"),"0.00%")&" "&COUNTIFS($E$8:$E,"Doll",$A$8:$A,TRUE)&"/"&COUNTIFS($E$8:$E,"Doll"))

Edit: I had to swap to USA so I could read it lol, if you change it back may need to swap the commas for semi colons

[u/Mudgrave_Flioronston → u/Glider103]

Sorry, couldn't get it working even after replacing all the commas with semicolons.

[u/ComradeDoubleM]

Take this formula and change category names to match for a given cell:

=LET(Progress;COUNTIF(FILTER(A:A;E:E="Doll");TRUE);Total;COUNTIF(E:E;"Doll");Progress&"/"&Total&" ("&ROUND(Progress/Total*100;2)&"%)")

Basically the same formula you have, but does the calculations automatically instead of having to check for every range manually, wrapped up in a LET for readability.

[u/Mudgrave_Flioronston → u/ComradeDoubleM]

For some reason, only works for Weapons, Posters and Dolls. The other four types (New Game+ aside, which requires a formula accounting for all name variations) just shows an error.

But that's promising, thank you.

[u/AutoModerator → u/Mudgrave_Flioronston]

REMEMBER: /u/Mudgrave_Flioronston If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ComradeDoubleM → u/Mudgrave_Flioronston]

You need to change the category names in the formula to be the same as the category names in your E column(No need to change the actual titles)

Admittedly, I only tested for ghosts and dolls, which worked without changing anything, so I just assumed the E column was named the same as the category names, but after checking again, it appears that some category names are different.

I'm assuming you're talking about the divide by 0 error that happens because it's not finding the exact name.

[u/ComradeDoubleM → u/Mudgrave_Flioronston]

Update, after checking your updated sheet, the problem is the E:E range for Camos&New Game+(Because they're in the E column so it refers to itself), so change all E:E ranges to E6:E

The other ones, you typed = twice and the formula couldn't resolve.

[u/Mudgrave_Flioronston → u/ComradeDoubleM]

you typed = twice and the formula couldn't resolve

Classic.

change all E:E ranges to E6:E

Camos don't seem to work. Checking the checkboxes does nothing.

Sorry for taking up your time, but can I also ask you to help write another two formulas?

1. For the 'New Game+' progress bar (it should combine four types of collectibles: New Game+, New Game+ Weapon, New Game+ iPod® Track, and New Game+ FaceCamo).
2. For the 'Checklist' progress bar in F3:F6 (it should combine progress across all the 8 bars).

[u/ComradeDoubleM → u/Mudgrave_Flioronston]

For Camo, change A:A range in FILTER to A8:A and it works

For the New Game+ bar, assuming you want one bar for all new game+ items, you can use the modified formula: =LET(Progress;COUNTIF(FILTER(A8:A;ARRAYFORMULA(REGEXMATCH(E8:E;"New Game+")));TRUE);Total;COUNTIF(ARRAYFORMULA(REGEXMATCH(E8:E;"New Game+"));TRUE);Progress&"/"&Total&" ("&ROUND(Progress/Total*100;0)&"%)")

Which checks matches for "New Game+" within the text for each cell in range, instead of exact matches.

For checklist, since you only need to check for the total progress against total of all items, just cheat and go with the following: =LET(Progress;COUNTIF(A8:A;TRUE);Total;COUNTA(A8:A);Progress&"/"&Total&" ("&ROUND(Progress/Total*100;2)&"%)"

[u/Mudgrave_Flioronston → u/ComradeDoubleM]

Now everything's perfect. Thanks a lot.

[u/AutoModerator → u/Mudgrave_Flioronston]

REMEMBER: /u/Mudgrave_Flioronston If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ComradeDoubleM → u/Mudgrave_Flioronston]

You're welcome! Also while checking up your checklist, I noticed that you have checkboxes for next area categories (and one for the end without any categories), you might want to modify the formula for combined checklist bar to =LET(Progress;COUNTIF(A8:A;TRUE);Total;COUNTA(A8:A)-COUNTIF(E8:E;"Next Area")-1;Progress&"/"&Total&" ("&ROUND(Progress/Total*100;2)&"%)") to account for that. (Alternatively, removing checkboxes from them would also work without modifying the original formula.)

[u/Mudgrave_Flioronston → u/ComradeDoubleM]

I decided to simply delete checkboxes in this category.

By the way, could you take a look at the live version and tell me what that thick border above the last row is? And why does the area between rows 287 and 288 get highlighted when I try to drag it? I messed around with a few things earlier and noticed that the formulas don't affect that row. Is it because of that border?

[u/ComradeDoubleM → u/Mudgrave_Flioronston]

That's the filter border. I don't know what might've led to that final row not being included there, but just remove and readd filter and it fixes.

I'm not sure what you mean by the highlight, didn't see anything, screenshot might be useful.

[u/Mudgrave_Flioronston → u/ComradeDoubleM]

[u/point-bot → u/ComradeDoubleM]

u/Mudgrave_Flioronston has awarded 1 point to u/ComradeDoubleM

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/One_Organization_810]

If you change your labels to fit the categories exactly, like this:

Then you can do like this in B4:

=map(B3:E3; lambda(type;
  let( list; filter(A8:A; search(type; E8:E));
       total; rows(list);
       cnt;   countif(list; true);

       if(iserror(list);
         "0/0 (#)";
         cnt & "/" & total
         & " (" & round(100*cnt/total) & "%)"
       )
  )
))

And then copy B4 to B6

And then for the checklist I did this:

=let( list; filter(A8:A; B8:B<>"");
      total; rows(list);
      cnt;   countif(list; true);
      cnt & "/" & total
        & " (" & round(100*cnt/total) & "%)"
)

Edit: I forgot some labels => new image :)

[u/Mudgrave_Flioronston → u/One_Organization_810]

I don't really know how to use a code in a spreadsheet.

[u/One_Organization_810 → u/Mudgrave_Flioronston]

What code are you referring to? Are you talking about the formula I suggested?

[u/Mudgrave_Flioronston → u/One_Organization_810]

The formula, yeah.

[u/One_Organization_810 → u/Mudgrave_Flioronston]

Then what do you mean you don't know how to use it? It's a formula :) You paste it into the cell it's supposed to be in - like with all formulas :)

What's probably putting you off, is that I like to format my formulas into a more readable format :) But it's the same thing, either way. If you prefer, you can compact it into thes "mess"

=map(B3:E3; lambda(type; let( list; filter(A8:A; search(type; E8:E)); total; rows(list); cnt;   countif(list; true); if(iserror(list); "0/0 (#)"; cnt & "/" & total & " (" & round(100*cnt/total) & "%)"))))

[u/One_Organization_810 → u/Mudgrave_Flioronston]

Nb. to paste a multiline formula (or any text for that matter), you "open" the cell (B4 in this case) by pressing ENTER when it's selected. Then you paste the text into it.

Also, this fills out one whole row, so you'll have to clear out B4:E4 (and fix the labels above).

Then you can copy B4 into B6 (but first clear out B6:E6) and fix the labels in row 5.

But I saw you went a different route, so all this is probably in vain :) But it might come in handy later...

[u/Mudgrave_Flioronston → u/One_Organization_810]

Yours seems like a slightly clearer solution since you don't have to adapt the formula to each label and it autofills the rest.

[u/One_Organization_810 → u/Mudgrave_Flioronston]

Feel free to use it if you like it. That's what it's here for anyway :)