If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct Question
I have a spreadsheet with 1-5k rows (and growing) with A-I columns -- Cols
A-G text fields, Cols H-I number fields. H&I are YTD totals. Can SUMPRODUCT() total (h&i) using data in 3 or 4 columns (a-g) as criteria? Columns A-E contain eName, dCode, jCode, pPeriod (1-12), and pCode for each person (400). Each person will have several different pCodes in each pPeriod. I would like to have (on a separate worksheet) the following: dCode jCode pPeriod pCode HrsYTD GrossYTD 500 3745 1 025 #### $$$$ 500 3745 1 725 #### $$$$ 500 3745 3 025 #### $$$$ 500 3745 4 025 #### $$$$ 500 0374 1 025 #### $$$$ 500 0374 2 055 #### $$$$ 500 0374 2 725 #### $$$$ 600 1445 1 020 #### $$$$ 600 1445 2 855 #### $$$$ 800 3335 4 025 #### $$$$ 900 2225 1 025 #### $$$$ 900 2225 3 055 #### $$$$ Office Pro Edition, Excel 2003 SP3 Thanks |
#2
|
|||
|
|||
Sumproduct Question
Yes.
Try in H2 =SUMPRODUCT(--(Sheet1!A2:A5000=A2),--(Sheet1!B2:B5000=B2),--(Sheet1!C2:C5000=C2),--(Sheet1!D2:A5000=D2),(H2:H5000)) Assuming detailed data on Sheet1 with dCode in Col A, jCode in B, pPeriod in C, and pCode in D with a header row. Change 5000 to the last row... and in I2 =SUMPRODUCT(--(Sheet1!A2:A5000=A2),--(Sheet1!B2:B5000=B2),--(Sheet1!C2:C5000=C2),--(Sheet1!D2:A5000=D2),(I2:I5000)) Performance might be an issue due to large amount of calculations involved... "ShagNasty" wrote: I have a spreadsheet with 1-5k rows (and growing) with A-I columns -- Cols A-G text fields, Cols H-I number fields. H&I are YTD totals. Can SUMPRODUCT() total (h&i) using data in 3 or 4 columns (a-g) as criteria? Columns A-E contain eName, dCode, jCode, pPeriod (1-12), and pCode for each person (400). Each person will have several different pCodes in each pPeriod. I would like to have (on a separate worksheet) the following: dCode jCode pPeriod pCode HrsYTD GrossYTD 500 3745 1 025 #### $$$$ 500 3745 1 725 #### $$$$ 500 3745 3 025 #### $$$$ 500 3745 4 025 #### $$$$ 500 0374 1 025 #### $$$$ 500 0374 2 055 #### $$$$ 500 0374 2 725 #### $$$$ 600 1445 1 020 #### $$$$ 600 1445 2 855 #### $$$$ 800 3335 4 025 #### $$$$ 900 2225 1 025 #### $$$$ 900 2225 3 055 #### $$$$ Office Pro Edition, Excel 2003 SP3 Thanks |
#3
|
|||
|
|||
Sumproduct Question
ShagNasty wrote:
I have a spreadsheet with 1-5k rows (and growing) with A-I columns -- Cols A-G text fields, Cols H-I number fields. H&I are YTD totals. Can SUMPRODUCT() total (h&i) using data in 3 or 4 columns (a-g) as criteria? Columns A-E contain eName, dCode, jCode, pPeriod (1-12), and pCode for each person (400). Each person will have several different pCodes in each pPeriod. I would like to have (on a separate worksheet) the following: dCode jCode pPeriod pCode HrsYTD GrossYTD 500 3745 1 025 #### $$$$ 500 3745 1 725 #### $$$$ 500 3745 3 025 #### $$$$ 500 3745 4 025 #### $$$$ 500 0374 1 025 #### $$$$ 500 0374 2 055 #### $$$$ 500 0374 2 725 #### $$$$ 600 1445 1 020 #### $$$$ 600 1445 2 855 #### $$$$ 800 3335 4 025 #### $$$$ 900 2225 1 025 #### $$$$ 900 2225 3 055 #### $$$$ Office Pro Edition, Excel 2003 SP3 Thanks You might want to consider a PivotTable. If you are not familiar with them, there are numerous tutorials and tips pages on the web. One is linked below. http://www.microsoft.com/dynamics/us...s_collins.mspx |
Thread Tools | |
Display Modes | |
|
|