MrExcel Publishing
Your One Stop for Excel Tips & Solutions

One more SUMIF array; thanks Aladin for earlier help


Posted by Jim on February 14, 2002 10:40 AM

Believe me, I've tried but for the life of me can't figure out how to make this work as an array or simplified formula.

=SUMIF(B5:AF5,"D",B6:AF6)+SUMIF(B7:AF7,"D",B8:AF8)+SUMIF(B9:AF9,"D",B10:AF10)+SUMIF(B11:AF11,"D",B12:AF12) and on, and on...

Thanks Aladin for help on similar COUNTIF as SUMPRODUCT, very ingenious!


Posted by Aladin Akyurek on February 14, 2002 11:01 AM

You're welcome.

Here another SUMPRODUCT, a companion:

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