Variable sum from defined input

[David]

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.

 

[T. Valko]

Why don't you tell us what the criteria is and we can eliminate the need to
input row numbers.

 

[David]

Unfortunately the criteria changes so defining which rows to sum is the
preferred way to go about it.

Hope that helps.

 

[T. Valko]

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 D13

=SUMPRODUCT((ISNUMBER(MATCH(ROW(A1:B10)-MIN(ROW(A1:B10))+1,D13,0)))*A1:B10)

 

[T. Valko]

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.

 

[David]

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.

 

[Shane Devenshire]

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.

 

[T. Valko]

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))