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.

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.

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.

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.

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.

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.

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.

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.