Feedback by UserVoice

Darren Van Slyke

1. Subtotal with Conditiona Countif & Sumif option for visible cells

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

Darren Van Slyke supported this idea  ·
2. Provide a SUBTOTALIF function to sum filtered data based on criteria - basically SUMIF on visible cells only.

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

An error occurred while saving the comment
Darren Van Slyke commented  ·

The only way to get conditional summary data that works with filters or tables is by using a complex SUMPRODUCT string with OFFSET and MIN. It's ridiculous. Surely making SUMIF work with filters should be an easy solution?

Example of a conditional subtotal that works with filters:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$S75:\$S77,ROW(\$S75:\$S77)-MIN(ROW(\$S75:\$S77)),,1)),(\$S75:\$S77="M")*(Z75:Z77)*(\$AQ75:\$AQ77="S10"))

Darren Van Slyke supported this idea  ·
3. New maths & stats functions

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

An error occurred while saving the comment
Darren Van Slyke commented  ·

The only way to do conditional subtotals of data in a table or so filters are recognized is to do a crazy formula like this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$S75:\$S77,ROW(\$S75:\$S77)-MIN(ROW(\$S75:\$S77)),,1)),(\$S75:\$S77="M")*(Z75:Z77)*(\$AQ75:\$AQ77="S10"))

Surely an easier formula variable could be developed that sums up data from a range when conditions are met AND the result is recognized by filters/tables. "SUMIF" should do it, but it doesn't work with filters.