SUMIF不包括隐藏的行。小计？ SUMPRODUCT？ [英] SUMIF excluding hidden rows. SUBTOTAL? SUMPRODUCT?

问题描述

我有两个大型的事件表。第一个表是具有日期，持续时间和代码（以识别事件）的数据表（ tblData ）。第二个表是每个事件占用总时间的汇总表（ tblSummary ）。

如何过滤 tblData 并更新 tblSummary ？

以下是 tblData 的一小部分。请注意，第2级代码有3列（ L2a ， L2b ， L2c ）;意思是，如果我正在寻找2级代码总长度 EL ，我必须检查所有3列，并添加他们的持续时间次。如下图所示，总共 EL 时间将为 9.9

下面是一个 tblSummary / p>

对于总计列，我使用 SUMIF（tblData [L2a]，[@ [Lvl 2 Code]]，tblData [Duration]）来获取代码的总时间但这包括隐藏的行。 tblSummary 在更改 tblData 之前的过滤器时不会更新。

我相信使用 SUBTOTAL 是一个死胡同。这将解决隐藏的列问题，但是由于没有办法设置标准，因此我无法将其应用于 tblSummary 。

我是新的 SUMPRODUCT 但我确实尝试使用

= SUMPRODUCT SUBTOTAL（109，tblData [Duration]）， - （tblData [L2a] =EL））

但收到＃VALUE！。而且我按了 Ctl + Shift + Enter 。

解决方案

tblSummary 结构化表 总计列（又称K15）是，

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

如果表格不执行，请填写。

当我们过滤呃 tblData 表格的日期列可隐藏2015年11月24日，2015年11月27日和2015年12月28日，新的总计显示在 tblSummary

< img src =https://i.stack.imgur.com/TXHvg.pngalt =sumif_hidden2>

---

¹_{当整个工作簿中的任何内容发生变化时，都会重新计算易失性函数，而不仅仅是影响其结果的内容发生变化。 volatile 函数的示例是 INDIRECT ， OFFSET ，今天，现在， RAND 和 RANDBETWEEN 。 CELL 和 INFO 工作表函数也会使它们变得不稳定。}

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.

解决方案

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.}

这篇关于SUMIF不包括隐藏的行。小计？ SUMPRODUCT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！