Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
range:
C3,C14,C25,C34,C41 criteria: 0 sum-range: C3,C14,C25,C34,C41 I can't get that to work since the commas in the range are throwing the function off. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Here is a possible solution to your problem. For example if A1 is greater than 0 then this formula would sum all the cells listed. If A1 is 0 or less then the result if "False". =IF(A10,SUM(D1,D3,D5,D7,D9,D11,D13,D15,D17),"Fals e") Hope it helps. BTW. there is a limit to the number of cells you can use this way but I can't remember how many it is. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php...o&userid=19838 View this thread: http://www.excelforum.com/showthread...hreadid=499168 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
One way...
=SUMPRODUCT(--(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0),CHOOSE( {1,2,3,4, 5},C3,C14,C25,C34,C41)) Hope this helps! In article , "Lady_Olara" wrote: range: C3,C14,C25,C34,C41 criteria: 0 sum-range: C3,C14,C25,C34,C41 I can't get that to work since the commas in the range are throwing the function off. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
=SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0"))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lady_Olara" wrote in message ... range: C3,C14,C25,C34,C41 criteria: 0 sum-range: C3,C14,C25,C34,C41 I can't get that to work since the commas in the range are throwing the function off. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
I like that Bob!
Even works without the "Product": =SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0")) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... =SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lady_Olara" wrote in message ... range: C3,C14,C25,C34,C41 criteria: 0 sum-range: C3,C14,C25,C34,C41 I can't get that to work since the commas in the range are throwing the function off. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Would you all be so kind as to explain your solutions? I have not even heard
of some of these functions before, and the help pages aren't written for someone who is really rusty on the technical side of Excel. Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Let's assume that C3, C14, C25, C34, and C41 contain the following
values... 100 0 150 125 -250 ....and we have the following formula... =SUMPRODUCT(--(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0),CHOOSE( {1,2,3,4, 5},C3,C14,C25,C34,C41)) ....here's how it breaks down... CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0 evaluates to: TRUE FALSE TRUE TRUE FALSE --(CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41)0) evaluates to: 1 0 1 1 0 Notice that the double negative coerces TRUE and FALSE to their numerical equivalent of 1 and 0, respectively. CHOOSE({1,2,3,4,5},C3,C14,C25,C34,C41) evaluates to: 100 0 150 125 -250 SUMPRODUCT multiplies these two arrays... =SUMPRODUCT({1,0,1,1,0},{100,0,150,125,-250}) ....which gives us... =SUMPRODUCT({100,0,150,125,0}) ....and which it sums and returns 375. Hope this helps! In article , "Lady_Olara" wrote: Would you all be so kind as to explain your solutions? I have not even heard of some of these functions before, and the help pages aren't written for someone who is really rusty on the technical side of Excel. Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Thanks RD.
Indeed it does work with just SUM, which makes it better IMO (must get help for this SP fetish I have <vbg). I like it better than Domenic's solution as it is much easier to add to (with Domenic's you have to add another entry to the array constants every time you add another cell), although his does have the advantage of being more easily copyable to other cells. Swings and roundabouts as always. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ragdyer" wrote in message ... I like that Bob! Even works without the "Product": =SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0")) -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... =SUMPRODUCT(SUMIF(INDIRECT({"C3","C14","C25","C34" ,"C41"}),"0")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lady_Olara" wrote in message ... range: C3,C14,C25,C34,C41 criteria: 0 sum-range: C3,C14,C25,C34,C41 I can't get that to work since the commas in the range are throwing the function off. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
In my solution, INDIRECT is used to get around the fact that SUMIF doesn't
accept a non-contiguous range by creating an array of values from those non-contiguous cells. Using Domenic's example values, it passes an array {100,0,150,125,-250} to SUMIF, which is test for greater than 0, "0", and this in turn passes an array {100,0,150,125,0}to the SUMPRODUCT function (or even SUM as RD points out). Note that the original -250 is transformed to 0 as it fails the 0 test. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lady_Olara" wrote in message ... Would you all be so kind as to explain your solutions? I have not even heard of some of these functions before, and the help pages aren't written for someone who is really rusty on the technical side of Excel. Thanks in advance. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
In article ,
"Bob Phillips" wrote: ...although his does have the advantage of being more easily copyable to other cells... ....and it doesn't include a volatile function, such as INDIRECT... :) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Good point, although that rarely becomes an issue, whereas maintainability
always is, and copying very often is. still like it better though <vbg Bob "Domenic" wrote in message ... In article , "Bob Phillips" wrote: ...although his does have the advantage of being more easily copyable to other cells... ...and it doesn't include a volatile function, such as INDIRECT... :) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
In article ,
"Bob Phillips" wrote: Good point, although that rarely becomes an issue, whereas maintainability always is, and copying very often is. Agreed... still like it better though <vbg Yep, I personally have no problem with it... :) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Ragdyer wrote: I like that Bob! Even works without the "Product": =SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0")) See also a previous occasion on the matter: http://www.mrexcel.com/board2/viewtopic.php?t=29977 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF non-contiguous range
Thanks for the link ... very interesting !
Goes to show that very little is really original around here. However, I'm sure that the second caveman who figured out about bringing a burning brand into the cave to produce light, felt no less a sense of accomplishment then the first one, since both were independently contrived .... NOT that I'm calling Bob a caveman!<vbg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... Ragdyer wrote: I like that Bob! Even works without the "Product": =SUM(SUMIF(INDIRECT({"C3","C14","C25","C34","C41"} ),"0")) See also a previous occasion on the matter: http://www.mrexcel.com/board2/viewtopic.php?t=29977 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
SumIf in Visible Cell Range | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |