SUMIF带有下列表标准 [英] SUMIF with listdown criteria

问题描述

我有3列分别命名为Team，Side，Score. 然后，我创建了一个包含All，Home和Away的Listdown，这将成为我的SUMIF中的标准.

I have a 3 columns naming Team,Side,Score. Then I created a Listdown which has All,Home, and Away then that will be my criteria in my SUMIF.

所以. =SUMIF(B2:B100,D1)其中D1是列表单元格.我在这里的目标是求和，如果我选择住家还是出门或选择其中一个(全部).但是我的边栏"只包含归宿".如果我在列表中选择全部"，我想同时计算住家"和离开"怎么办?抱歉，我无法解释清楚.

so. =SUMIF(B2:B100,D1) where D1 is the listdown cell. My goal here is to sum if I choose home or away or either(all). But my Side column only contains Home and Away. What will I do if I want to sum both Home and Away if I pick All in the List? Sorry, I can't explain well.

推荐答案

使用通过ctr + shift + enter最终确定的数组公式，而不仅仅是输入.

Use this array formula finalized with ctr+shift+enter, not just enter.

'for the count
=SUM(COUNTIF(B:B, IF(D1="all", {"home","away"}, D1)))
'for the sum of scores in column c
=SUM(SUMIFS(C:C, B:B, IF(D1="all", {"home","away"}, D1)))

这篇关于SUMIF带有下列表标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！