Darren Van Slyke
My feedback

5 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Darren Van Slyke supported this idea ·

13 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Darren Van Slyke supported this idea · 
14 votes5 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Darren Van Slyke commentedThe 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.

3 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Darren Van Slyke supported this idea ·
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"))