Hi

Try the array entered formula
{=SUM(IF($M$5:$M$1000=C4,$B$5:$J$1000)}

Use Ctrl+Shift+Enter to commit or amend the formula.


--
Regards

Roger Govier



dlh wrote:
Is there any way of accomplishing the following in fewer words:

=SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$B $5:$B$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$C $5:$C$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$D $5:$D$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$E $5:$E$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$F $5:$F$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$G $5:$G$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$H $5:$H$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$I $5:$I$1000)
+SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),jan!$J $5:$J$1000)

I thought an array formula might help, but the following doesn't work:

{=SUMIF(jan!$M$5:$M$1000,concatenate("=",C4),offse t(jan!$B$5:$B$1000,0,{0,1,2,3,4,5,6,7,8}))}

In other words, how can I get SUMIF to loop through two dimensions?
The most annoying part about it is whenever I click in the cell, most
of the top of the worksheet is hidden behind the long formula
displayed in the edit box.

-dlh


--
dlh
------------------------------------------------------------------------
dlh's Profile:
http://www.excelforum.com/member.php...o&userid=26113 View
this thread: http://www.excelforum.com/showthread...hreadid=497498