Match & SUM certain cells in Column and Row and display data

[coccio]

An example I have is


A2: A5 has my account #
B1: E1 listed my code


3AA 3AB 3AE 3AD
100 33 8 5 7
110 45 0 8 0
120 5 4 5 5
130 7 5 5 6

Cell G1 look up condition 100
Cell H1 lookup condition 3AA

I want to match G1 and H1 and display it in a ceratin cell

Such has A7

I use this formula:

=OFFSET(A1,MATCH(G1,$A$2:A$7,0),MATCH(H1,$B$1:E$1,0))

THAT WORKS FINE but what I want to do is in column A Is I want to sum the numbers les than 120 in this case for 3AA it should be = 78

Is there a SUM fuction that can preform that.

[Aladin Akyurek]

On 2002-05-20 06:53, coccio wrote:
An example I have is


A2: A5 has my account #
B1: E1 listed my code


3AA 3AB 3AE 3AD
100 33 8 5 7
110 45 0 8 0
120 5 4 5 5
130 7 5 5 6

Cell G1 look up condition 100
Cell H1 lookup condition 3AA

I want to match G1 and H1 and display it in a ceratin cell

Such has A7

I use this formula:

=OFFSET(A1,MATCH(G1,$A$2:A$7,0),MATCH(H1,$B$1:E$1,0))

THAT WORKS FINE but what I want to do is in column A Is I want to sum the numbers les than 120 in this case for 3AA it should be = 78

Is there a SUM fuction that can preform that.

In I1 enter: 120

In A8 enter:

=SUM(OFFSET(A1,MATCH(G1,$A$2:A$7,0),MATCH(H1,$B$1:E$1,0)):OFFSET(A1,MATCH(I1,$A$2:A$7,0)-1,MATCH(H1,$B$1:E$1,0)))

What is the purpose Coccio?

Aladin

[coccio]

Thanks Aladin,

This is just an example. I have to a lot of numbers to sum. Like the 100 acounts are assets and I need to sum it and display it in a cell.

Just a follow up question.

If I wanted it to be greater than 120?

[Aladin Akyurek]

On 2002-05-20 08:14, coccio wrote:
Thanks Aladin,

This is just an example. I have to a lot of numbers to sum. Like the 100 acounts are assets and I need to sum it and display it in a cell.

Just a follow up question.

If I wanted it to be greater than 120?

By changing the values of G1 and I1, I'd think. If not, try to be more specific about the task you want to accomplish.

Aladin

[coccio]

Sorry aladin,
Let me example a bit better I think I'm getting a bit confused.

I have a table set up:
A2:A8 has account #
B1:E1 listed my code


3AA00 3AA00 3AE20 3AA00
100 3 0 0 0
110 0
115 1
116 4
118 5
120 44 6,767.00 0
130 44 98 8.00 0

what I want to do is sum the values in column
B Greater than account #120 and less than Acoount #120 and display then in two diffrent cells.

I hope that is clearer.

Thank You for help.
Coccio

[coccio]

Also the table is a pivot table and the account # changes from time to time so the cells will change. I need to have it so i don't have to change a fixed formula. I can still sum the account # that need to be summed.

[coccio]

I feel pretty stupid. Thanks for you help again Aladdin.
I made something complicated when it wasn't nesseasey

=SUMIF(A2:A10,"<120",B2:B10)

coccio

[Aladin Akyurek]

On 2002-05-20 08:39, coccio wrote:
Sorry aladin,
Let me example a bit better I think I'm getting a bit confused.

I have a table set up:
A2:A8 has account #
B1:E1 listed my code


3AA00 3AA00 3AE20 3AA00
100 3 0 0 0
110 0
115 1
116 4
118 5
120 44 6,767.00 0
130 44 98 8.00 0

what I want to do is sum the values in column
B Greater than account #120 and less than Acoount #120 and display then in two diffrent cells.

I hope that is clearer.

Thank You for help.
Coccio

I get confused too.
You have now the same code (that is, 3AA00) 3 times in B1:E1. Why is that? That would make the distinction impossible.

You want to sum values in B (presumably related to the code 3AA00) for Acc#'s less than 120 in one cell and for Acc#'s greater than 120 in a different cell. What do you want to do with value associated with 120 itself?

Aladin

[coccio]

That was a poor example. I didn't state it clearly. I just wanted to sum values in a column that was less than a certain number and I used this simply formula and it worked out fine

=SUMIF(A2:A10,"<120",B2:B10)

Thanks

coccio