Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
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" 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,"="&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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
RagDyeR wrote: How about a nice, simple, single formula of: =SUMPRODUCT((M5:M1000=C4)*(B5:J1000)) That is already done. See Roger's post. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2D sumif loop?
..
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" wrote in message ... RagDyeR wrote: How about a nice, simple, single formula of: =SUMPRODUCT((M5:M1000=C4)*(B5:J1000)) That is already done. See Roger's post. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Loop gone crazy | Excel Discussion (Misc queries) | |||
Do Loop | Excel Discussion (Misc queries) | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |