Excel-基于条件对数据集中的值求和 [英] Excel - Sum values from the data set based on criteria

问题描述

我在Excel中有一个庞大的数据集(具有30万至40万个观测值).我现在想创建一个表格，该表格将基于特定条件(例如年份和国家/地区)的数据集中的值相加.为此，我使用sumif:

I have a huge data set (with between 300 000 - 400 000 observations) in Excel. I now want to make a table that sums values from the data set based a certain criteria, like year and country. For that purpose I use sumif:

=SUMIFS('Data'!F:F; 'Data'!$A:$A;$A2)

$ A2是国家/地区(我还有四个输入条件B1-E1，此处未显示).

Where $A2 is the country (I have four more input criteria, B1-E1, that are not shown here).

这很好，除了我希望代码忽略空条件的问题.也就是说，如果我不输入国家/地区的值，则希望它对所有国家/地区的数据求和，而忽略空的条件.

This works fine, except for the problem that I want the code to ignore empty criteria. That is, if I do not put in a value for country, I want it to sum the data for all countries and ignore the empty criteria.

有什么建议吗?

有关标准(A1-E1)，请参见下表:

edit: See table below for the criteria (A1-E1):

Land    Region    Code  Name of product Year
Sweden  Stockholm 52    Apple           1995

推荐答案

尝试

=SUMIFS('Data'!F:F; 'Data'!$A:$A; if($A2=""; "*"; A2))   'all column A including blanks
'alternate
=SUMIFS('Data'!F:F; 'Data'!$A:$A; if($A2=""; "<>"; A2))   'all column A except blanks

这篇关于Excel-基于条件对数据集中的值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！