Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Hi all,
I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
There may well be Ben, but it would help to know what the cells are. Give us
an example of data and what you want. -- HTH RP (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Ben,
Copy the code below into a module of the workbook where you want the formula, and use it like this in your sheet: =mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") where the separate ranges are listed inside parens, with commas between. You can copy down to match the range that you want. HTH, Bernie MS Excel MVP Function mySumIf(inRange As Range, inArg As String) As Double Dim myArea As Range For Each myArea In inRange.Areas mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg) Next myArea End Function "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Bob,
Basically, here's what I wanted to do: let's say I have 3 rows: 1, 2, 3. and I have 10 columns of numbers(A to J), but I only want to sum the even column if it's greater than zero. That is if columns: B, D, F, H, J are each greater than zero than sum it up. Or sum up the columns that are greater than 0 in this is of columns. But since it's not contiguous, the range in the sumif part of the formula, breaks and cannot be used, at least the way I was trying to do it. But Bernie's method worked. Thanks. Ben -- "Bob Phillips" wrote: There may well be Ben, but it would help to know what the cells are. Give us an example of data and what you want. -- HTH RP (remove nothere from the email address if mailing direct) "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Bernie,
It worked. Thank you so much for that great suggestion! Ben -- "Bernie Deitrick" wrote: Ben, Copy the code below into a module of the workbook where you want the formula, and use it like this in your sheet: =mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") where the separate ranges are listed inside parens, with commas between. You can copy down to match the range that you want. HTH, Bernie MS Excel MVP Function mySumIf(inRange As Range, inArg As String) As Double Dim myArea As Range For Each myArea In inRange.Areas mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg) Next myArea End Function "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Bernie,
I do wanted to ask if you can give further explanation as to why it works. Not quite clear on the recursive call. Thanks again. Ben -- "Bernie Deitrick" wrote: Ben, Copy the code below into a module of the workbook where you want the formula, and use it like this in your sheet: =mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") where the separate ranges are listed inside parens, with commas between. You can copy down to match the range that you want. HTH, Bernie MS Excel MVP Function mySumIf(inRange As Range, inArg As String) As Double Dim myArea As Range For Each myArea In inRange.Areas mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg) Next myArea End Function "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Ben,
It's not a recursive call. To assign a value to a function (the value that it returns) you assign a value using FunctionName = Value mySumIf = Value Since it is stepping through the areas of the range that is passed to it, and doing the SUMIF piece-meal, we need to remember the old value, and add any new value to it: mySumIf = mySumIf + Value The first time through, mySumIf has a value of 0, then it is assigned the value of the first area of the range being passed, then increased by the second area..... So, when you use mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") it is the same as using SUMIF(C1,"0") + SUMIF(E1:H1,"0") + .... + SUMIF(M1:O1,"0") If you want to see how it does it in action, put a break into the function, and then press Ctrl-Alt-F9 from within Excel, then step through the function. HTH, Bernie MS Excel MVP "Ben" wrote in message ... Bernie, I do wanted to ask if you can give further explanation as to why it works. Not quite clear on the recursive call. Thanks again. Ben -- "Bernie Deitrick" wrote: Ben, Copy the code below into a module of the workbook where you want the formula, and use it like this in your sheet: =mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") where the separate ranges are listed inside parens, with commas between. You can copy down to match the range that you want. HTH, Bernie MS Excel MVP Function mySumIf(inRange As Range, inArg As String) As Double Dim myArea As Range For Each myArea In inRange.Areas mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg) Next myArea End Function "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF
Thanks Bernie. That was a very elegant solution. Much appreciated.
Ben -- "Bernie Deitrick" wrote: Ben, It's not a recursive call. To assign a value to a function (the value that it returns) you assign a value using FunctionName = Value mySumIf = Value Since it is stepping through the areas of the range that is passed to it, and doing the SUMIF piece-meal, we need to remember the old value, and add any new value to it: mySumIf = mySumIf + Value The first time through, mySumIf has a value of 0, then it is assigned the value of the first area of the range being passed, then increased by the second area..... So, when you use mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") it is the same as using SUMIF(C1,"0") + SUMIF(E1:H1,"0") + .... + SUMIF(M1:O1,"0") If you want to see how it does it in action, put a break into the function, and then press Ctrl-Alt-F9 from within Excel, then step through the function. HTH, Bernie MS Excel MVP "Ben" wrote in message ... Bernie, I do wanted to ask if you can give further explanation as to why it works. Not quite clear on the recursive call. Thanks again. Ben -- "Bernie Deitrick" wrote: Ben, Copy the code below into a module of the workbook where you want the formula, and use it like this in your sheet: =mySumIf((C1,E1:H1,J1:K1,M1:O1),"0") where the separate ranges are listed inside parens, with commas between. You can copy down to match the range that you want. HTH, Bernie MS Excel MVP Function mySumIf(inRange As Range, inArg As String) As Double Dim myArea As Range For Each myArea In inRange.Areas mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg) Next myArea End Function "Ben" wrote in message ... Hi all, I am wondering if there's a way to use SUMIF(range, criteria) if the range is not a contiguous, ie, the data is on the same row but in non-contiguous columns. I am looking to use it in my code to replace a very long formula. But I tried to use it in the formula, where the range is not contiguous. But that didn't work. Is there any way to make it work with non contiguous ranges? If there is, I would also like to copy down this column with the same formula to work on the corresponding row. Thanks for sharing your thought. Ben -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |