software

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

=SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$B $5:$B$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$C $5:$C$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$D $5:$D$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$E $5:$E$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$F $5:$F$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$G $5:$G$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$H $5:$H$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$I $5:$I$1000)
SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,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(quot;=quot;,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: www.excelforum.com/member.php...oamp;userid=26113
View this thread: www.excelforum.com/showthread...hreadid=497498Hi

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 gt; wrote:
gt; Is there any way of accomplishing the following in fewer words:
gt;
gt; =SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$B $5:$B$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$C $5:$C$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$D $5:$D$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$E $5:$E$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$F $5:$F$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$G $5:$G$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$H $5:$H$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$I $5:$I$1000)
gt; SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),jan!$J $5:$J$1000)
gt;
gt; I thought an array formula might help, but the following doesn't work:
gt;
gt; {=SUMIF(jan!$M$5:$M$1000,concatenate(quot;=quot;,C4),offse t(jan!$B$5:$B$1000,0,{0,1,2,3,4,5,6,7,8}))}
gt;
gt; In other words, how can I get SUMIF to loop through two dimensions?
gt; The most annoying part about it is whenever I click in the cell, most
gt; of the top of the worksheet is hidden behind the long formula
gt; displayed in the edit box.
gt;
gt; -dlh
gt;
gt;
gt; --
gt; dlh
gt; ------------------------------------------------------------------------
gt; dlh's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26113 View
gt; this thread: www.excelforum.com/showthread...hreadid=497498
Why not create a total per record in column N, by means of:

=SUM(B5:J5)

and invoking a simple and fast SumIf formula:

=SUMIF(jan!$M$5:$M$1000,quot;=quot;amp;C4,jan!$N$5:$N$1000)

Roger Govier wrote:
gt; Hi
gt;
gt; Try the array entered formula
gt; {=SUM(IF($M$5:$M$1000=C4,$B$5:$J$1000)}
gt;
gt; Use Ctrl Shift Enter to commit or amend the formula.
gt;
gt;

How about a nice, simple, single formula of:

=SUMPRODUCT((M5:M1000=C4)*(B5:J1000))

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------quot;Aladin Akyurekquot; gt; wrote in message
...
Why not create a total per record in column N, by means of:

=SUM(B5:J5)

and invoking a simple and fast SumIf formula:

=SUMIF(jan!$M$5:$M$1000,quot;=quot;amp;C4,jan!$N$5:$N$1000)

Roger Govier wrote:
gt; Hi
gt;
gt; Try the array entered formula
gt; {=SUM(IF($M$5:$M$1000=C4,$B$5:$J$1000)}
gt;
gt; Use Ctrl Shift Enter to commit or amend the formula.
gt;
gt;


RagDyeR wrote:
gt; How about a nice, simple, single formula of:
gt;
gt; =SUMPRODUCT((M5:M1000=C4)*(B5:J1000))
gt;

That is already done. See Roger's post.

..

Unless I'm missing a post, all I see there is an *array* formula.lt;ggt;
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

quot;Aladin Akyurekquot; gt; wrote in message
...RagDyeR wrote:
gt; How about a nice, simple, single formula of:
gt;
gt; =SUMPRODUCT((M5:M1000=C4)*(B5:J1000))
gt;

That is already done. See Roger's post.