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
|
|||
|
|||
SumIf or SumIfs or something else?
I have a worksheet with a list of names like this
A Staff Joe Suzie Sally Sam And another worksheet that has a pivot table of data that I need to sum across certain columns A B C D E F G Staff 4/1 4/2 4/6 4/7 4/8 4/9 Joe 4 2 9 1 Suzie 2 3 5 3 Sally 10 14 10 8 12 Sam 9 2 4 12 13 7 How can I give the results of the columns D through G where the name matches the other sheet? And also, I will then need the average of culumns D through G, excluding zeros! I can't it to work using the following formula =SUMIFS('[Outcalls - April.xls]Rep By Day'!$E:$M,'[Outcalls - April.xls]Rep By Day'!$A:$A,"="&A2) |
#2
|
|||
|
|||
SumIf or SumIfs or something else?
For clarity, you said you want D:G, you example shows B:G, and your formula
calls out E:M. Not sure which one you really want, but I'll go with D:G. Summation: =SUMPRODUCT(('[Outcalls - April.xls]Rep By Day'!$A:$A=A2)*('[Outcalls - April.xls]Rep By Day'!$D:$G)) Average =SUMPRODUCT(('[Outcalls - April.xls]Rep By Day'!$A:$A=A2)*('[Outcalls - April.xls]Rep By Day'!$D:$G))/SUMPRODUCT(('[Outcalls - April.xls]Rep By Day'!$A:$A=A2)*('[Outcalls - April.xls]Rep By Day'!$D:$G0)) Do note that this formula assumes you don't have any text in columns D:G. Having dates is okay, as those are technically numbers, but don't ahve any text labels. Otherwise, you would need to limit range, and not callout entire columns. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "crmccurry" wrote: I have a worksheet with a list of names like this A Staff Joe Suzie Sally Sam And another worksheet that has a pivot table of data that I need to sum across certain columns A B C D E F G Staff 4/1 4/2 4/6 4/7 4/8 4/9 Joe 4 2 9 1 Suzie 2 3 5 3 Sally 10 14 10 8 12 Sam 9 2 4 12 13 7 How can I give the results of the columns D through G where the name matches the other sheet? And also, I will then need the average of culumns D through G, excluding zeros! I can't it to work using the following formula =SUMIFS('[Outcalls - April.xls]Rep By Day'!$E:$M,'[Outcalls - April.xls]Rep By Day'!$A:$A,"="&A2) |
#3
|
|||
|
|||
SumIf or SumIfs or something else?
Excel 2007
PivotTable, Table, GetPivotData, three more ways. http://www.mediafire.com/file/zwgonjmjz0g/04_18_09.xlsx |
Thread Tools | |
Display Modes | |
|
|