Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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:$G<0)) 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf or SumIfs or something else?
Excel 2007
PivotTable, Table, GetPivotData, three more ways. http://www.mediafire.com/file/zwgonjmjz0g/04_18_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Sumifs | Excel Worksheet Functions | |||
sumifs, sumif with dates | Excel Worksheet Functions | |||
SUMIF and SUMIFS | Excel Worksheet Functions | |||
SUMIFS and OR | Excel Discussion (Misc queries) | |||
SUMIF or SUMIFS | Excel Worksheet Functions |