SUMIF excluding hidden rows. SUBTOTAL? SUMPRODUCT?

Question

I have 2 massive tables of events. The first table is a data table (tblData) that has dates, durations, and codes (to identify the event). The second table is a summary table (tblSummary) of the total time each event took.

How can I filter tblData and update tblSummary?

Below is a small part of tblData. Notice there are 3 columns for Level 2 codes (L2a,L2b,L2c); meaning if I am looking for total duration of Level 2 Code "EL", I have to check all 3 columns and add their Duration times. Given just the picture below, total "EL" time would be 9.9

Below is a piece of tblSummary.

For the Total column, I used SUMIF (tblData[L2a], [@[Lvl 2 Code]], tblData[Duration]) to get the total time of a code but this includes hidden rows. tblSummary does not update when I change the filter on tblData.

I believe using SUBTOTAL is a dead end. This would solve the hidden columns issue, but since there is no way to set criteria I cannot apply it to tblSummary.

I am new to SUMPRODUCT but I did try to use

=SUMPRODUCT(SUBTOTAL(109,tblData[Duration]),--(tblData[L2a]="EL"))

but received #VALUE!. And I did press Ctl + Shift + Enter.

Answer 1

Coupling together the SUMIF function's column value condition while ignoring hidden rows means we have to force an extra layer of calculation on a SUBTOTAL function and collect the aggregate with the cyclic calculation of the SUMPRODUCT function. The extra 'layer' of processing will be accomplished through the OFFSET function¹.

    

The standard formula in the first row of the tblSummary structured table Total column (aka K15) is,

=SUMPRODUCT((SUBTOTAL(103, OFFSET(tblData, ROW(tblData)-ROW(INDEX(tblData, 1, 0)), 0, 1, 1))=1)*(tblData[L2a]=[@[Lvl 2 Code]]), tblData[Duration])

Fill down if the table does not perform this for you.

When we filter the tblData table's Date column to hide 24-Nov-2015, 27-Nov-2015 and 28-Nov-2015, the new totals show in the tblSummary table.

    

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}