excel 2016将小计与sumifs相结合 [英] excel 2016 combine subtotal with sumifs

问题描述

我的SUMIFS需要加D列(D11:D172)，并且第一个条件是一个称为qbplistcomp的范围(B列，所以B11:B172)用于多个匹配(例如CHF，COPD和所有STROKE)，因此请使用STROKE*)，第二个条件在C列(C11:C172)中，查找该条件，该条件是标签"qbp量" ...到目前为止，没有小计，此公式的工作方式为:

My SUMIFS needs to add up column D (D11:D172) and the first criteria is a range called qbplistcomp (which is column B so B11:B172) for multiple hits (e.g. CHF, COPD, and all STROKE so using STROKE*) and the second criteria is in column C (C11:C172) looking for the criteria which is a label "qbp volumes" ... so far without subtotaling, this formula works as:

= SUM(SUMIFS(D11:D172，qbplistcomp，{"CHF"，"COPD"，"STROKE *"}，C11:C172，"QBP卷"))现在，我的A列可以按类别进行过滤，因此，如果我选择说中风"，则应该只对那些内容进行小计(总和)...继续出错...帮助，谢谢，蒂娜

=SUM(SUMIFS(D11:D172,qbplistcomp,{"CHF","COPD","STROKE*"},C11:C172,"QBP Volumes")) Now my column A has the ability to filter on Category, so if I choose say just "stroke" it should only subtotal (sum) just those ones ... keep getting error ... help, thanks, Tina

推荐答案

有趣的问题.为什么?它为我们提供了一个将SumIfs和小计相结合的非常多维的公式.

Interesting question.. Why? It gives us a very multidimensional fomula combining SumIfs and Subtotal.

我的示例公式. = SUMPRODUCT((A6:A17 = A2)*(B6:B17 = {"North"，"West"，"East"})*(SUBTOTAL(103，OFFSET(B6，ROW(B6:B17)-MIN(ROW(B6:B17))，0)))*(C6:C17))

因此您的公式应该是 SUMPRODUCT((C11:C172 ="QBP Volumes")*(B11:B172 = {"CHF"，"COPD"，"STROKE *"})*(SUBTOTAL(103，OFFSET(B11，ROW(B11:B172)-MIN(ROW(B11:B172))，0)))*(D11:D172))

请参阅何时未针对北部"过滤B列C2 = 38859，即北部和西部的A总计

See When the B Col is NOT filtered for "North" C2= 38859 i.e. total for A in North and West

当B列被过滤为北"时，C2 = 32313，即总计北仅A

When the B Col is filtered for "North" C2= 32313 i.e. total for A ONLY in North

请记住，小计公式适用于您应该过滤的列(Col B).如果您打算将Col c设置为相同的值，则用小计公式替换(C11:C172 ="QBP Volumes")，这样您的新公式将为 SUMPRODUCT((SUBTOTAL(103，OFFSET(C11，ROW(C11:C172)-MIN(ROW(C11:C172))，0)))*(B11:B172 = {"CHF"，"COPD"，"STROKE *"})*(SUBTOTAL(103，OFFSET(B11，ROW(B11:B172)-MIN(ROW(B11:B172))，0)))*(D11:D172))

Remember, subtotal formula is applicable to the column you are supposed to filter(Col B). If you intend the same for Col c then replace(C11:C172="QBP Volumes") with subtotal formula so your new formula will be SUMPRODUCT((SUBTOTAL(103,OFFSET(C11,ROW(C11:C172)-MIN(ROW(C11:C172)),0)))*(B11:B172={"CHF","COPD","STROKE*"})*(SUBTOTAL(103,OFFSET(B11,ROW(B11:B172)-MIN(ROW(B11:B172)),0)))*(D11:D172))

这篇关于excel 2016将小计与sumifs相结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！