Sum values in one row dependent on a value in a different row

In Row 5 I have 9 cells with the text value of S, W or R, 3 of each - ie S,
R, S, W, W, S, R, R, W. This is a random order that changes in each table.

In Row 9 there is a different numerical value in each Cell. I want to sum
the cell values in Row 9 that correspond to the 3 columns that have the S,
the R and the W in them.

I started using an IF fn thus: IF(B5:J5 "(S)", and the test
gives me the correct string (True, False, True, True etc) but then I got
stuck. I don't think this is the correct function to use, so...

How do I get one cell to see what text value (S or R or W) is in Row 5, and
sum the 3 resulting cells in Row 9 that correspond to the text value in the
same column in Row 5?

I think I have over-complicated my question so can clarify if any confusion.

Many thanks,

Mike

Try this:

=Sumif(B5:J5,"S",B9:J9)

You could assign a cell to contain your criteria, so that changing the
contents of the cell would total the different letters.

Say you used B1 for your variable:

=Sumif(B5:J5,B1,B9:J9)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mike of Tilford" Mike of wrote in
message ...
In Row 5 I have 9 cells with the text value of S, W or R, 3 of each - ie
S,
R, S, W, W, S, R, R, W. This is a random order that changes in each
table.

In Row 9 there is a different numerical value in each Cell. I want to sum
the cell values in Row 9 that correspond to the 3 columns that have the S,
the R and the W in them.

I started using an IF fn thus: IF(B5:J5 "(S)", and the
test
gives me the correct string (True, False, True, True etc) but then I got
stuck. I don't think this is the correct function to use, so...

How do I get one cell to see what text value (S or R or W) is in Row 5,
and
sum the 3 resulting cells in Row 9 that correspond to the text value in
the
same column in Row 5?

I think I have over-complicated my question so can clarify if any
confusion.

Many thanks,

Mike

Try

=SUMPRODUCT((B5:J5="S")*(B9:J9))

And substitute S with W & R

Mike

"Mike of Tilford" wrote:

In Row 5 I have 9 cells with the text value of S, W or R, 3 of each - ie S,
R, S, W, W, S, R, R, W. This is a random order that changes in each table.

In Row 9 there is a different numerical value in each Cell. I want to sum
the cell values in Row 9 that correspond to the 3 columns that have the S,
the R and the W in them.

I started using an IF fn thus: IF(B5:J5 "(S)", and the test
gives me the correct string (True, False, True, True etc) but then I got
stuck. I don't think this is the correct function to use, so...

How do I get one cell to see what text value (S or R or W) is in Row 5, and
sum the 3 resulting cells in Row 9 that correspond to the text value in the
same column in Row 5?

I think I have over-complicated my question so can clarify if any confusion.

Many thanks,

Mike

Brilliant - Thanks. Both suggestions work.

"Mike H" wrote:

Try

=SUMPRODUCT((B5:J5="S")*(B9:J9))

And substitute S with W & R

Mike

"Mike of Tilford" wrote:

In Row 5 I have 9 cells with the text value of S, W or R, 3 of each - ie S,
R, S, W, W, S, R, R, W. This is a random order that changes in each table.

In Row 9 there is a different numerical value in each Cell. I want to sum
the cell values in Row 9 that correspond to the 3 columns that have the S,
the R and the W in them.

I started using an IF fn thus: IF(B5:J5 "(S)", and the test
gives me the correct string (True, False, True, True etc) but then I got
stuck. I don't think this is the correct function to use, so...

How do I get one cell to see what text value (S or R or W) is in Row 5, and
sum the 3 resulting cells in Row 9 that correspond to the text value in the
same column in Row 5?

I think I have over-complicated my question so can clarify if any confusion.

Many thanks,

Mike

Brilliant - Thanks. Both posts work.

"Ragdyer" wrote:

Try this:

=Sumif(B5:J5,"S",B9:J9)

You could assign a cell to contain your criteria, so that changing the
contents of the cell would total the different letters.

Say you used B1 for your variable:

=Sumif(B5:J5,B1,B9:J9)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mike of Tilford" Mike of wrote in
message ...
In Row 5 I have 9 cells with the text value of S, W or R, 3 of each - ie
S,
R, S, W, W, S, R, R, W. This is a random order that changes in each
table.

In Row 9 there is a different numerical value in each Cell. I want to sum
the cell values in Row 9 that correspond to the 3 columns that have the S,
the R and the W in them.

I started using an IF fn thus: IF(B5:J5 "(S)", and the
test
gives me the correct string (True, False, True, True etc) but then I got
stuck. I don't think this is the correct function to use, so...

How do I get one cell to see what text value (S or R or W) is in Row 5,
and
sum the 3 resulting cells in Row 9 that correspond to the text value in
the
same column in Row 5?

I think I have over-complicated my question so can clarify if any
confusion.

Many thanks,

Mike