Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
I have two columns (assume A1:B10) that contain specific results. Of those
results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Why don't you tell us what the criteria is and we can eliminate the need to
input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Unfortunately the criteria changes so defining which rows to sum is the
preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Ok, do you want to input the actual row numbers or the *relative* row
numbers? Relative meaning as it relates to the row numbers of the table no matter where the table is? For example, if your data is as you say in A1:B10 and you want to sum row 1 then A1 and B1 would be summed. If your data is A22:B32 and you want to sum row 1 *relative* to the table then the sum would be A22:B22. This formula will handle both situations but we can shorten it slightly if you want the *actual* row numbers. Assume you enter the row numbers in the range D1:D3 =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D1:D3,0)))*A1:B10) -- Biff Microsoft Excel MVP "David" wrote in message ... Unfortunately the criteria changes so defining which rows to sum is the preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Correction:
This formula will handle both situations The formula will work on *relative* row numbers. But, if your data really is in A1:B10 then it'll work in that situation also. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, do you want to input the actual row numbers or the *relative* row numbers? Relative meaning as it relates to the row numbers of the table no matter where the table is? For example, if your data is as you say in A1:B10 and you want to sum row 1 then A1 and B1 would be summed. If your data is A22:B32 and you want to sum row 1 *relative* to the table then the sum would be A22:B22. This formula will handle both situations but we can shorten it slightly if you want the *actual* row numbers. Assume you enter the row numbers in the range D1:D3 =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D1:D3,0)))*A1:B10) -- Biff Microsoft Excel MVP "David" wrote in message ... Unfortunately the criteria changes so defining which rows to sum is the preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
I will try to be more clear...
A B 1 20 30 2 30 40 3 40 50 4 50 60 5 60 70 6 70 80 7 80 90 8 90 100 9 100 110 10 110 120 OK, so a i have my two column (A and B). Lets say that my criteria is met in rows 3 to 8 only. I want to input these rows and get the sum for each column at the specified row range. "T. Valko" wrote: Correction: This formula will handle both situations The formula will work on *relative* row numbers. But, if your data really is in A1:B10 then it'll work in that situation also. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, do you want to input the actual row numbers or the *relative* row numbers? Relative meaning as it relates to the row numbers of the table no matter where the table is? For example, if your data is as you say in A1:B10 and you want to sum row 1 then A1 and B1 would be summed. If your data is A22:B32 and you want to sum row 1 *relative* to the table then the sum would be A22:B22. This formula will handle both situations but we can shorten it slightly if you want the *actual* row numbers. Assume you enter the row numbers in the range D1:D3 =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D1:D3,0)))*A1:B10) -- Biff Microsoft Excel MVP "David" wrote in message ... Unfortunately the criteria changes so defining which rows to sum is the preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Hi,
You're making this hard on yourself. Suppose you enter the criteia in C1 (and D1 - you didn't tell us if there could be one or more criteria?). =SUMPRODUCT(--(A1:A10+B1:B10)*(A1:A10=C1)) This way you just enter your criteria in however many cells you need to specify it. Of course if you were a little clearer on what type of criteria, we could do a better job. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "David" wrote: I will try to be more clear... A B 1 20 30 2 30 40 3 40 50 4 50 60 5 60 70 6 70 80 7 80 90 8 90 100 9 100 110 10 110 120 OK, so a i have my two column (A and B). Lets say that my criteria is met in rows 3 to 8 only. I want to input these rows and get the sum for each column at the specified row range. "T. Valko" wrote: Correction: This formula will handle both situations The formula will work on *relative* row numbers. But, if your data really is in A1:B10 then it'll work in that situation also. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, do you want to input the actual row numbers or the *relative* row numbers? Relative meaning as it relates to the row numbers of the table no matter where the table is? For example, if your data is as you say in A1:B10 and you want to sum row 1 then A1 and B1 would be summed. If your data is A22:B32 and you want to sum row 1 *relative* to the table then the sum would be A22:B22. This formula will handle both situations but we can shorten it slightly if you want the *actual* row numbers. Assume you enter the row numbers in the range D1:D3 =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D1:D3,0)))*A1:B10) -- Biff Microsoft Excel MVP "David" wrote in message ... Unfortunately the criteria changes so defining which rows to sum is the preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable sum from defined input
Ok, try this...
The input row numbers being *realtive* to the range. D1 = 3 E1 = 8 =SUM(INDEX(A1:A10,D1):INDEX(A1:A10,E1)) -- Biff Microsoft Excel MVP "David" wrote in message ... I will try to be more clear... A B 1 20 30 2 30 40 3 40 50 4 50 60 5 60 70 6 70 80 7 80 90 8 90 100 9 100 110 10 110 120 OK, so a i have my two column (A and B). Lets say that my criteria is met in rows 3 to 8 only. I want to input these rows and get the sum for each column at the specified row range. "T. Valko" wrote: Correction: This formula will handle both situations The formula will work on *relative* row numbers. But, if your data really is in A1:B10 then it'll work in that situation also. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, do you want to input the actual row numbers or the *relative* row numbers? Relative meaning as it relates to the row numbers of the table no matter where the table is? For example, if your data is as you say in A1:B10 and you want to sum row 1 then A1 and B1 would be summed. If your data is A22:B32 and you want to sum row 1 *relative* to the table then the sum would be A22:B22. This formula will handle both situations but we can shorten it slightly if you want the *actual* row numbers. Assume you enter the row numbers in the range D1:D3 =SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D1:D3,0)))*A1:B10) -- Biff Microsoft Excel MVP "David" wrote in message ... Unfortunately the criteria changes so defining which rows to sum is the preferred way to go about it. Hope that helps. "T. Valko" wrote: Why don't you tell us what the criteria is and we can eliminate the need to input row numbers. -- Biff Microsoft Excel MVP "David" wrote in message ... I have two columns (assume A1:B10) that contain specific results. Of those results, some data is within a specification and some is not. I want to be able to input which rows are to be summed in a cell and then the summed rows result in another. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding files in a folder. Variable not defined error. | Excel Discussion (Misc queries) | |||
Validation: - different messages for defined input values | Excel Discussion (Misc queries) | |||
Input formulas in a defined data range and convert results as valu | New Users to Excel | |||
User Defined/Custom Input | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |