Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells which have common Row and Column headings
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells which have common Row and Column headings
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 "Leanne at Work" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add cells which have common Row and Column headings
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 "Mike H" wrote: 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 "Leanne at Work" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet has numeric column headings. Change to alpha headings? | Excel Discussion (Misc queries) | |||
Column headings to numbers and row headings to alphabets? | Excel Discussion (Misc queries) | |||
Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
can u add common info to a cells in a column with out doing @ cel | Excel Discussion (Misc queries) | |||
Can I invert a table so row headings are now column headings etc | Excel Worksheet Functions |