MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simplify formula as array?


Posted by Jim on February 14, 2002 7:26 AM

Can this be written simpler as an array formula?

=COUNTIF(B5:AF5,"D")+COUNTIF(B7:AF7,"D")+COUNTIF(B9:AF9,"D")+COUNTIF(B11:AF11,"D")+COUNTIF(B13:AF13,"D")+COUNTIF(B15:AF15,"D")+COUNTIF(B17:AF17,"D")+COUNTIF(B19:AF19,"D")+COUNTIF(B21:AF21,"D")+COUNTIF(B23:AF23,"D")+COUNTIF(B25:AF25,"D")+COUNTIF(B27:AF27,"D")


Posted by Aladin Akyurek on February 14, 2002 9:10 AM

Jim --

Try:

=SUMPRODUCT((MOD(ROW(B5:AF19),2)<>0)*((B5:AF19&"")="D"))

Normally entered.

Aladin

======================