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

[Mike of Tilford]

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

 

[Ragdyer]

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)

 

[Mike H]

Try

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

And substitute S with W & R

Mike

 

[Mike of Tilford]

Brilliant - Thanks. Both suggestions work.

Try

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

And substitute S with W & R

Mike

 

[Mike of Tilford]

Brilliant - Thanks. Both posts work.

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)