Add cells which have common Row and Column headings

[Leanne at Work]

Hello,

I think I might have better luck trying to explain myself by providing an
example first.
I have the following data:

A B C D E F G
1 Wk1 Wk2 Wk3 Wk4 Wk5 Wk6
2 REP 1.2007 1.2007 1.2007 2.2007 2.2007 2.2007
2 John 5 5 3 5 6
3 John 2 1 5 8 7
4 Jack 2 6 1 2

I am trying to create a formula in another spreadsheet that will sum the
total months (1.2007 etc) for the respective Reps. So for 1.2007, John will
have the total of 16 and Jack will have 8. For 2.2007 John will have a total
of 31 and so on.
The data I'm working with has varying duplicate row and column entries of
Reps and months - and these are likely to change as the data is refreshed.

Does anyone possibly know of a formula that will provide this data?

Thank you very much for your time.

 

[Mike H]

Leanne,

It's not obvious to me exactly what you have in the header row 1. This
assumes you have a number 1.2007 etc.

=SUMPRODUCT((A2:A20="John")*(B1:G1=1.2007)*(B2:G20))

Mike

 

[Mike H]

Ah,

Now I see you fooled me with your typo for the rows. With your table like
this try

=SUMPRODUCT((A3:A20="John")*(B2:G2=1.2007)*(B3:G20))

I would use cell references in the formula but have used the values to make
it clearer what is happening si this is better

=SUMPRODUCT((A3:A20=H1)*(B2:G2=I1)*(B3:G20))

A B C D E F G
1 Wk1 Wk2 Wk3 Wk4 Wk5 Wk6
2 REP 1.2007 1.2007 1.2007 2.2007 2.2007 2.2007
3 John 5 5 3 5 6
4 John 2 1 5 8 7
5 Jack 2 6 1 2


Mike