EXCEL2007版
1.新建模拟数据EXCEL工作表;
2.选中D2:D15,定义名称为“金额”;
3. 选中C2:C15,定义名称为“项目”;
4. 选中B2:B15,定义名称为“地区”;
5. 选中A2:A15,定义名称为“姓名”;
6.为了更加直观的看到SUMIFS函数与SUMPRODUCT函数的区别,我们设置二个函数的比较区,采用的条件完全相同;
7.假设我们设置的条件为:对于同时满足“姓名=王一,地区=地区一,项目=项目一”的行求和;
8. SUMIFS 函数公式1:“=SUMIFS(金额,姓名,'王一',地区,'地区一',项目,'项目一')”,这里条件的参数直接写成条件值;
9. SUMPRODUCT函数的公式1:“=SUMPRODUCT((姓名='王一')*(地区='地区一')*(项目='项目一')*金额)”,这里条件的参数直接写成条件值;
10. SUMIFS 函数公式2:“=SUMIFS(金额,姓名,$G$3,地区,$H$3,项目,$I$3)”,这里条件的参数写成单元格引用;
11. SUMPRODUCT函数的公式2:“=SUMPRODUCT((姓名=$G$10)*(地区=$H$10)*(项目=$I$10)*金额)”,这里条件的参数写成单元格引用;
12.上面4个公式的计算结果都是一样的,都是下图中红色方框中的数值的合计数;
13.假设我们要求“地区一范围内王姓人员的全部项目对应的金额之和”,则SUMIFS 公式1:“=SUMIFS(金额,姓名,'王*:',地区,'地区一',项目,'项目*')”,这里“姓名”和“项目”使用了通配符;
14. SUMPRODUCT公式1: “=SUMPRODUCT((姓名='王*')*(地区='地区一')*(项目='项目*')*金额)”,这里“姓名”和“项目”也使用了通配符;
15.从计算的结果来看,SUMIFS函数计算正确,SUMPRODUCT函数没有计算出结果。这一点就是SUMIFS函数与SUMPRODUCT函数重要区别:SUMIFS函数可以使用通配符,SUMPRODUCT函数不能够使用通配符;
16.假设我们再更换一组条件,计算姓名为“*八”的人员的全部项目、全部地区对应的金额之和,那么SUMIFS 公式1:“=SUMIFS(金额,姓名,'*八',地区,'地区*',项目,'项目*')”,计算结果正确。
1.请注意“SUMIFS函数可以使用通配符,SUMPRODUCT函数不能够使用通配符”,请根据实际情况选用。
2. SUMIFS函数可以最多设置127个条件,SUMPRODUCT函数可以设置2-30个参数。