在這篇文章中，探討一種計(jì)算在至少一列中滿足規(guī)定條件的行數(shù)的解決方案，示例工作表如下圖1所示，其中詳細(xì)列出了各個(gè)國(guó)家在不同年份廢鎳的出口水平。
圖3
此時(shí)，我們根本無(wú)法按照上述方法構(gòu)造與SUMPRODUCT函數(shù)解決方案等效的COUNTIFS構(gòu)造。使用SUMPRODUCT函數(shù)的公式：
=SUMPRODUCT(N((B2:B14>=1000)+(C2:C14>=1000)+(D2:D14>=1000)+(E2:E14>=1000)+(F2:F14>=1000)+(G2:G14>=1000)+(H2:H14>=1000)+(I2:I14>=1000)+(J2:J14>=1000)>0))
可得出正確的結(jié)果11。然而，公式顯得太笨拙了，如果考慮的列數(shù)不是9而是30，那會(huì)怎樣!
幸運(yùn)的是，由于示例中列區(qū)域是連續(xù)的，因此可以在單個(gè)表達(dá)式中查詢整個(gè)區(qū)域(B2：J14)，隨后適當(dāng)?shù)夭倏v這個(gè)結(jié)果數(shù)組。
這個(gè)解決方案的數(shù)組公式如下：
=SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0))
公式中，比較區(qū)域中的每個(gè)元素是否大于或等于1000：
B2:J14>1000
結(jié)果是包含以下布爾值的數(shù)組：
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
使用N函數(shù)強(qiáng)制轉(zhuǎn)換成數(shù)字：
N(B2:J14>1000)
得到：
{1,1,1,1,1,1,1,1,1;0,0,1,1,1,1,1,1,1;0,0,1,1,0,1,1,1,1;0,0,0,0,0,1,1,1,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1;0,1,0,0,0,0,1,0,1;1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1}
現(xiàn)在，為了計(jì)算每一行中1的數(shù)量，我們使用MMULT。并且，由于上述數(shù)組(一個(gè)13行乘9列的數(shù)組)包含9列，因此我們用來(lái)形成乘積的矩陣的行數(shù)必須等于該數(shù)組的列數(shù)。這樣，形成第二個(gè)矩陣的公式構(gòu)造為：
TRANSPOSE(COLUMN(B2:J14)^0)
轉(zhuǎn)換為：
TRANSPOSE({2,3,4,5,6,7,8,9,10}^0)
轉(zhuǎn)換為：
TRANSPOSE({1,1,1,1,1,1,1,1,1})
由于必須確保由9個(gè)1組成的數(shù)組由9行組成，因此使用TRANSPOSE函數(shù)來(lái)轉(zhuǎn)換：
{1;1;1;1;1;1;1;1;1}
這樣，就可以將上述兩個(gè)數(shù)組傳遞給MMULT函數(shù)，因此：
MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))
轉(zhuǎn)換為：
MMULT({1,1,1,1,1,1,1,1,1;0,0,1,1,1,1,1,1,1;0,0,1,1,0,1,1,1,1;0,0,0,0,0,1,1,1,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,1,1;0,1,0,0,0,0,1,0,1;1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1;1,1,1,1,1,1,1,0,1;0,0,0,0,0,0,0,0,0;1,1,1,1,1,1,1,1,1},{1;1;1;1;1;1;1;1;1})
得到：
{9;7;6;3;9;9;3;9;0;9;8;0;9}
然后，確定該數(shù)組中哪些元素大于0，然后將結(jié)果求和。因此，公式：
=SUM(N(MMULT(N(B2:J14>1000),TRANSPOSE(COLUMN(B2:J14)^0))>0))
轉(zhuǎn)換為：
=SUM(N({9;7;6;3;9;9;3;9;0;9;8;0;9}>0))
轉(zhuǎn)換為：
=SUM(N({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}))
轉(zhuǎn)換為：
=SUM({1;1;1;1;1;1;1;1;0;1;1;0;1})
結(jié)果為：
11
如果不想使用數(shù)組公式，可以使用下面的公式替換：
=SUM(N(MMULT(N(B2:J14>1000),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B2:J14)))^0)>0))
