2D sumif loop?

[dlh]

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),offset(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

[Roger Govier]

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 <[email protected]> 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),offset(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

[Aladin Akyurek]

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,"="&C4,jan!$N$5:$N$1000)

Roger Govier wrote:
> 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.
>
>

[RagDyeR]

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 !
----------------------------------------------------------------------------
-------------------


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
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,"="&C4,jan!$N$5:$N$1000)

Roger Govier wrote:
> 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.
>
>

[Aladin Akyurek]

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

That is already done. See Roger's post.

[RagDyeR]

..

Unless I'm missing a post, all I see there is an *array* formula.<g>
--

Regards,

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

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...


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

That is already done. See Roger's post.