多條件數(shù)組在sumif、countif中的用法簡介

表一,,,表二,,,表三,,,,,,,,

店鋪,商品,銷售量,店鋪,商品,銷售量,店鋪,商品,銷售量,,,,,,

A001,狗皮膏,134,A001,狗皮膏,84,A002,狗皮膏,126,,gouweicao78,,,,

B001,狗尾草,64,B001,狗尾草,149,B001,狗尾草,56,,,,,,

A002,狗不理,85,B002,狗不理,191,A002,狗不理,58,,2006/3/14,,,,

B001,狗年旺,63,B001,狗年旺,166,B001,狗年旺,52,,,,,,

B002,狗皮膏,167,B002,狗皮膏,53,B002,狗皮膏,52,,返回引題,,,,

A002,狗尾草,25,A002,狗尾草,12,B003,狗尾草,109,,,,,,

A003,狗不理,142,A001,狗不理,174,A003,狗不理,53,,,,,,

A001,狗年旺,166,A002,狗年旺,71,A001,狗年旺,125,,,,,,

為方便比較，售量均為隨機(jī)數(shù)據(jù)，按F9可更新;sheet2、sheet3中的表二表三均引用此二表，數(shù)據(jù)一致。,,,,,,,,,,,,,,

SUMIF函數(shù)幫助：,,,,,,,,,,,,,,

,"SUMIF(range,criteria,sum_range)",,,,,,,,,,,,,

,Range

為用于條件判斷的單元格區(qū)域。,,,,,,,,,,,,,

,"Criteria

為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、""32""、"">32""或""apples""。",,,,,,,,,,,,,

,Sum_range

是需要求和的實際單元格。,,,,,,,,,,,,,

,"我們知道，sumif是一個很有用的條件求和函數(shù)，由于函數(shù)第一參數(shù)Range的限制（即條件在同列并保持與Sum_range水平距離一致，見經(jīng)典用法2），平時大家只用到了sumif函數(shù)的單條件求和，當(dāng)條件較多時，因為多條件的條件往往不在同一列，可借助輔助列用&文本合并符來并用sumif完成，不過一般多條件求和被sum(條件1*條件2*……*統(tǒng)計區(qū)域)數(shù)組公式或者sumproduct的用法所代替。

本文想闡述的是用sum(sumif()*{})結(jié)合的一種“單列”多條件求和的方法（countif的用法類似，就只在右邊列出公式而不另外闡述了）。",,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,求滿足左邊條件的店鋪個數(shù),,,,

,復(fù)習(xí)一下Sumif的經(jīng)典用法吧,,,,,,,,,,,,,

經(jīng)典用法1、,求表一的A字頭號店鋪的銷售量,,,,,,,,,,,,,

sumif,552,"=SUMIF(A3:A10,""A*"",C3)",,,,,552,,,5,家,,,

sum數(shù)組,552,"=SUM((LEFT(A3:A10)=""A"")*C3:C10)",,,,,552,,,"=COUNTIF(A3:A10,""A*"")",,,,

,sumif的第3個參數(shù)可以省略寫為區(qū)域的第一個單元格，有關(guān)用法見：,,,,,,,,,5,,,,

,/dispbbs.asp?BoardID=3&ID=136995,,,,,,,,,,,,,

,,,,,,,1255,,,,,,,

,,,,,,,1255,,,,,,,

經(jīng)典用法2、,求表一至表三（sheet1中）的A字頭號店鋪的銷售量,,,,,,,,,,,,,

sumif,1255,"=SUMIF(A3:G10,""A*"",C3)",,,,,,,,13,家,,,

sum數(shù)組,1255,"=SUM((LEFT(A3:A10)=""A"")*C3:C10,(LEFT(D3:D10)=""A"")*F3:F10,(LEFT(G3:G10)=""A"")*I3:I10)",,,,,,,,"=COUNTIF(A3:G10,""A*"")",,,,

,sumif的“條件在同列并保持與Sum_range水平距離一致”——這里的“同列”指的是店鋪名稱以A開頭這個條件在A、D、G這3列即同一字段名下，而非“以A開頭的店鋪的狗不理銷售量”這種條件跨了不同字段。特此說明。,,,,,,,,,13,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

經(jīng)典用法3、,求表一至表三（注：分別在sheet1~sheet3中）的A字頭店鋪的銷售量,,,,,,,,,,,,,

跨表條件求和,1255,"=SUM(SUMIF(INDIRECT(""sheet""&{1,2,3}&""!A3:A10""),""A*"",INDIRECT(""sheet""&{1,2,3}&""!C3"")))",,,,,,,,13,家,,,

,,,,,,,,,,"=SUM(COUNTIF(INDIRECT(""sheet""&{1,2,3}&""!A3:A10""),""A*""))",,,,

,,,,,,,,1255,,,,,,

,{}雙條件數(shù)組在Sumif中的應(yīng)用,,,,,,,1255,,13,,,,

以上3個用法都是我們常用的，且在論壇上例子也很多?，F(xiàn)在來個不常用的：,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,求表一中A字頭店鋪但不要A001的銷售量,,,,,,,,,,,,,

sumif,252,"=SUM(SUMIF(A3:A10,{""A*"",""A001""},C3)*{1,-1})",,,,,公式1,252,,3,家,,,

sum數(shù)組,252,"=SUM((LEFT(A3:A10)=""A"")*(A3:A10<>""A001"")*C3:C10)",,,,,,252,,"=SUM(COUNTIF(A3:A10,{""A*"",""A001""})*{1,-1})",,,,

,252,"=SUMIF(A3:A10,""A*"",C3)-SUMIF(A3:A10,""A001"",C3)",,,,,,252,,3,,,,

,"其實很簡單：公式1就是sumif(A3:A10,""A*"",C3)-sumif(A3:A10,""A001"",C3)的變體",,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,求表一至表三（sheet1中）A字頭店鋪但不要A001的銷售量,,,,,,,572,,8,家,,,

,572,"=SUM(SUMIF(A3:G10,{""A*"",""A001""},C3)*{1,-1})",,,,,,,,"=SUM(COUNTIF(A3:G10,{""A*"",""A001""})*{1,-1})",,,,8

,572,"=SUM((LEFT(A3:A10)=""A"")*(A3:A10<>""A001"")*C3:C10,(LEFT(D3:D10)=""A"")*(D3:D10<>""A001"")*F3:F10,(LEFT(G3:G10)=""A"")*(G3:G10<>""A001"")*I3:I10)",,,,,,,,,,,,

,求表一至表三（sheet1中）A字頭店鋪的銷售量，但A001的售量只算其3成。,,,,,,,,,,,,,

,342,"=SUM(SUMIF(A3:A10,{""A*"",""A001""},C3)*{1,-0.7})",,,,,公式2,776.9,,,,,,

,342,"=SUMIF(A3:A10,""A*"",C3)-SUMIF(A3:A10,""A001"",C3)*0.7",,,,,,,,,,,,

,,,,,,,公式2只算表一而已，可能有誤——盧子,,,,,,,

,{}多條件數(shù)組在Sumif中的應(yīng)用,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

4,,求左表中<-1或者>5且不等于9的數(shù)據(jù)之和,,,,,,,,,,,,

9,,,,,,,,,,,,,,

9,3,"=SUM(SUMIF(A63:A82,{""<-1"","">5"",""=9""})*{1,1,-1})",,,,,公式3,3,,,,,,

17,3,=SUM(((A63:A82<-1)+(A63:A82>5)*(A63:A82<>9))*A63:A82),,,,,,3,,,,,,

1,,,,,,,,,,,,,,

-9,,求左表中<-1或者>5且不等于9的數(shù)據(jù)個數(shù),,,,,,,,,,,,

9,,,,,,,,,,,,,,

-11,14,"=SUM(COUNTIF(A63:A82,{""<-1"","">5"",""=9""})*{1,1,-1})",,,,,,14,,,,,,

-18,14,=SUM((A63:A82<-1)+(A63:A82>5)*(A63:A82<>9)),,,,,,,,,,,,

6,,,,,,,,,,,,,,

6,,紅燈，對于公式3請注意,,,,,,,,,,,,

-11,,題目是,,,,,,,,,,,,

20,,“求左表中<-1或者>5且不等于9的數(shù)據(jù)之和”,,,,,,,,,,,,

-8,,很容易誤導(dǎo)人產(chǎn)生錯誤的解法：,,,,,,,,,,,,

13,18,"=SUM(SUMIF(A63:A82,{""<-1"","">5"",""<>9""})*{1,1,-1})",,,,,錯誤,,,,,,,

-11,,事實上，這個錯誤的公式求得的是：,,,,,,,,,,,,

-17,,"SUMIF(A63:A82,""<-1"")+SUMIF(A63:A82,"">5"")-SUMIF(A63:A82,""<>9"")",,,,,,,,,,,,

16,,也就是前2個條件分別求和并相加后減去第3個條件“對所有不等于9的求和”！,,,,,,,,,,,,

4,,,,,,,,,,,,,,

10,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,多單元格條件數(shù)組在Sumif中的應(yīng)用,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

產(chǎn)品名稱,產(chǎn)品等級,產(chǎn)品數(shù)量,,題目：,,,,,,,,,,

狗皮膏,A,20,,已知狗皮膏的等級及數(shù)量如左表，價格如下表，求這批產(chǎn)品共值多少錢。,,,,,,,,,,

狗皮膏,D,30,,,,,,,,,,,,

狗皮膏,C,45,,A,B,C,D,,,,,,,

狗皮膏,A,21,,100,80,50,20,,,,,,,

狗皮膏,B,20,,,,,,,,,,,,

狗皮膏,A,15,幫助理解,56,53,45,30,總價,12690,,,,,

狗皮膏,B,33,,5600,4240,2250,600,12690,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,12690,"=SUM(SUMIF(B87:B93,E89:H89,C87)*E90:H90)",,,,公式4,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,由于水平有限，希望文中描述能對您有所幫助，若有疑義可在帖子上跟貼討論。,,,,,gouweicao78,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,2006/3/14,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,返回引題,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,

,,,,,,,,,,,,,