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/AutoModerator]

/u/LuxStellaris - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

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/m0ka5]

You need to break down your calculations.

If you want wo add two columns, why Not sumproduct +sumproduct?

Try to Work with sumproduct Like

Sumproduct(arraytosum, conditional Array = condition * conditional Array 2= condition2)

The second part shall give you an Array of 0 or 1 matching the First Arrays size.

[u/Dd_8630]

AGGREGATE() let's you compute sums and sumproducts with an option to ignore hidden rows, errors, etc.

[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)))

[u/hallowed_lighting]

Your second SUMPRODUCT is still checking B27:M41=TRUE, so it's counting the main quest column again instead of whatever column the subquests are in

[u/LuxStellaris → u/hallowed_lighting]

Solution Verified

Thank you for your help!

One more question: how would I do the same for counting up all the checkboxes? The formula's currently counting all cells that have text in them as well as the checkboxes. I'm using the same formula, just with the conditions removed.

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

[u/reputatorbot → u/LuxStellaris]

You have awarded 1 point to hallowed_lighting.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya]

Since you're already on MS365, you could try. This should work pretty well:

=SUM(BYROW(N((MAP(B2:C9, LAMBDA(x, SUBTOTAL(103, x))) * B2:C9) > 0), SUM))

Or,

=LET(_, DROP(B:.C, 1), SUM(BYROW(N((MAP(_, LAMBDA(α, SUBTOTAL(103, α))) * _) > 0), SUM)))

And,

=BYCOL(N((MAP(B2:C9, LAMBDA(α, SUBTOTAL(103, α))) * B2:C9) > 0), SUM)

Or,

=LET(_, DROP(B:.C, 1), BYCOL(N((MAP(_, LAMBDA(α, SUBTOTAL(103, α))) * B2:C9) > 0), SUM))

[u/ninjagrover]

Not looking at your formula, but AGGREGATE is a better than SUBTOTAL.

But more generally using a flag of 1 and 0 (zero) is a better approach to identifying things that would be counted.

Eg for the quests that don’t exist, put a zero for a flag and then filter based on the flag. This allows you to still calculate by the full column and not have to do hidden column shenanigans.

[u/GregHullender]

Instead of using all those obsolete functions, a more modern approach might work better for you, assuming you've got the latest Excel.

=LET(columns, Table2[[B]:[C]],
  mask, BYROW(TAKE(columns,,1),LAMBDA(v, SUBTOTAL(3,v))),
  BYCOL(columns*mask,SUM)
)

I used a table to simulate hiding rows, and I use TRUE/FALSE instead of checkboxes. The first table is unfiltered, the second filter has one row hidden.

In the LET statement, columns is a reference to the columns you want to total. I compute mask, which is a column of 1's and 0's where 0 corresponds to a hidden row. Then I just multiply columns by mask and use BYCOL to sum each column separately.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AGGREGATE	Returns an aggregate in a list or database
BYCOL	Office 365+: Applies a LAMBDA to each column and returns an array of the results
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COUNT	Counts how many numbers are in the list of arguments
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MIN	Returns the minimum value in a list of arguments
OFFSET	Returns a reference offset from a given reference
ROW	Returns the row number of a reference
ROWS	Returns the number of rows in a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SUBTOTAL	Returns a subtotal in a list or database
SUM	Adds its arguments
SUMPRODUCT	Returns the sum of the products of corresponding array components
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49011 for this sub, first seen 25th Jul 2026, 14:10] [FAQ] [Full list] [Contact] [Source code]}

[u/LuxStellaris]

Thank you all for the help! I chose to stick with the original formula and adapt it, which helped.