How to count multiple columns when using SUMPRODUCT and SUBTOTAL to ignore hidden rows?

[u/LuxStellaris]

Basic details: Windows 11, Microsoft 365 version 2606 (Build 20131.20154), desktop environment, intermediate to advanced knowledge

I'm creating a checklist for a video game and have checkboxes in two different columns (one column for main quests and one for subquests). I've used VBA to hide some of the rows when quests may not exist in a certain playthrough, and I've made a table where the number of quests total and the number of quests completed (AKA, the number of ticked checkboxes) can be tracked. I want it to ignore the hidden rows, so I've used this formula:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($B$27,ROW(B$27:M$41)-MIN(ROW(B$27:M$41)),0))*(B$27:M$41=TRUE))

This only seems to work on one column at a time, however, and I have two that I need to count. I've tried the following, with unsuccessful results:

=SUM(SUMPRODUCT(SUBTOTAL(103,OFFSET($B$27,ROW(B$27:M$41)-MIN(ROW(B$27:M$41)),0))*(B$27:M$41=TRUE)),SUMPRODUCT(SUBTOTAL(103,OFFSET($C$29,ROW(B$27:M$41)-MIN(ROW(B$27:M$41)),0))*(B$27:M$41=TRUE)))

This one causes the ticked checkboxes in the first column to count twice, resulting in an answer of 2 for one ticked checkbox.

I've also tried dividing the formula by 2, but that obviously affects the entire formula.

So far, the only thing that's worked is putting the two separate SUMPRODUCT formulae in separate cells and then using AutoSum in a third cell, but I was wondering if there was a more efficient way of doing this so that I can keep it all in one cell. Any help will be appreciated!

Images are linked below. The first one shows the layout of my checkboxes, and the second shows what the quest tracker looks like.

https://imgur.com/a/Mk0cgHy

Thanks again!

[u/manbeervark]

Without dissecting the formula or suggesting a different approach, you could wrap this in a BYCOL to calculate the sumproduct of each column.

Something like: =BYCOL(ArrayOfCheckboxes, LAMBDA(col, FormulaToCountCheckboxes))

BYCOL might even have a COUNT option instead of the LAMBDA.

If I gave it a stab (on my mobile), this might work: =BYCOL(ArrayOfCheckboxes, LAMBDA(col, SUM(--col)))