10.18.2 How come my SUM(IF()) array formula doesn't work? (spreadsheets: Excel)
Description
This article is from the Spreadsheets FAQ, by Russell Schulz casfaq@locutus.ofB.ORG with numerous contributions by others.
10.18.2 How come my SUM(IF()) array formula doesn't work? (spreadsheets: Excel)
Excel (even v8, Excel 97) does not handle AND() or OR() in array
formulas; other versions have also had problems with MIN() and
MAX() in array formulas.
see http://support.microsoft.com/support/kb/articles/q77/6/76.asp
see ftp://ftp.microsoft.com/deskapps/excel/kb/q77/6/
but I recommend avoiding their booleans-are-numbers type coercion,
by changing
IF(and(a=b,c=d),x,y)
to
IF( if(a=b,1,0) * if(c=d,1,0) <>0 ,x,y)
IF(or(a=b,c=d),x,y)
to
IF( if(a=b,1,0) + if(c=d,1,0) <>0 ,x,y)
it makes the formulas longer, but should make your intent clearer to
everyone who didn't use this to get AND/OR in BASIC 20 years ago.
for AND(), you can just use multiplication (since only 1*1=1 and
0*anything=0) and SUM() the results. but for OR(), this won't
work (since 1+1=2). however, you can use the SIGN() function
when SUM()ing; e.g.,
SUM(SIGN(IF(A1:A10=6,1,0)+IF(B1:B10=22)))
will result in the count of rows where A1:A10 is 6 and B1:B10 is 22,
since SIGN(0) is 0 while SIGN(1) and SIGN(2) are both 1.
Continue to:
My Books
