Tracking portfolio evidence shouldn't feel like a puzzle with missing pieces. Yet here we are: a user with a perfectly reasonable spreadsheet, two columns of checkboxes, and a COUNTIF formula that only sees half the picture. The frustration is real, and it's also entirely fixable. Our take is straightforward: the solution lies in rethinking how you count, not in adding complexity.
The core problem is that your current formula is counting only one column because that's what you told it to do. When some criteria need two pieces of evidence and others need one, you're right that a simple COUNTIF/COUNTA ratio breaks down. The trick is to treat the entire range of checkboxes, both columns C and D, as a single set of tasks, then divide the number of checked boxes by the total number of boxes that *should* be checked. That total isn't just the number of rows; it's the number of rows times the required evidence count per row. So if row 3 needs two checks, that row contributes 2 to the denominator. If row 4 needs one check, it contributes 1.
Practically, this means you need two helper columns, or a smarter formula that accounts for the variable requirements. One approach: add a column that lists the number of required checks per row (1 or 2). Then your percentage formula becomes: `=SUM(COUNTIF(C:C,TRUE),COUNTIF(D:D,TRUE)) / SUM(that_helper_column)`. That gives you a clean percentage out of 100. If you want to avoid helper columns, you can hardcode the denominator by manually counting the total required checks, say, 12 rows with a mix of singles and doubles equals 18 required checks. But that's brittle; add a row and the formula breaks. The helper column is the more maintainable path.
This isn't about mastering a new function. It's about seeing your data as a map of requirements, not just a grid of cells. The spreadsheet already holds the logic of what needs to happen, you just need to translate that logic into a formula that counts the right things. Once you do, that percentage will reflect real progress, not a partial view. And that's the whole point: tools should work for you, not the other way around. So give yourself permission to add one extra column. It's a small step that turns a confusing calculation into a reliable dashboard.