Sumproduct Maybe?

[jlkirk]

On Worksheet 2 I have 100 columns. In row 1 are names, some of which are repeated. In row 4 is a one letter code, either an "L" or an "R". Rows 5 through 5000 are data in each of the cells in each column. In row 5001 is an average of the data in each column. What I would like to do is sum all of the avarages that correspond to the same name, and same code letter into a cell, say A1 on wortksheet 1.

For example, on worksheet 2, in row 1, column A is the name "John". John also appears in row 1, columns E, F, and G. In the corresponding cells in row 4 are an "R", "L", "R", and "R", respectively. What I would like to do is add all of the averages in row 5001 where the entries in the corresponding cells in rows 1 and 5 are John and "R". In my example, cells A5001, F5001 and G5001 would be sumed at Worksheet 1, cell A1. Any ideas?
Thanks in advance.

 

[Robert Mika]

Looking at your example is would be something lihe that
Array formula
Confirm CTRL+SHIFT+ENTER:
=SUM(IF(Sheet2!$A$1:$CC$1="John",IF(Sheet2!$A$4:$CC$4="R",Sheet2!$A$5001:$CC$5001)))

but that will only work for row 4.
Is that what you need?

 

[jlkirk]

Thanks. Unfortunately it doesn't work for me. I am coming up wit a zero, not the number it should be.

 

[barry houdini]

Robert's formula should work for you if correctly confirmed with CTRL+SHIFT+ENTER. If you do that correctly you'll get curly braces like { and } around the formula.

.......or you can use SUMPRODUCT as you suggested, which doesn't require "array entering", i.e.

=SUMPRODUCT((Sheet2!$A$1:$CC$1="John")*(Sheet2!$A$4:$CC$4="R"),Sheet2!$A$5001:$CC$5001)

or in Excel 2007 or later you can use SUMIFS

=SUMIFS(Sheet2!$A$5001:$CC$5001,Sheet2!$A$1:$CC$1,"John",Sheet2!$A$4:$CC$4,"R")

 

[jlkirk]

Thanks everyone. Yes, I just forgtot the ctrl shift enter sequence.