SumIf or SumIfs or something else?

[crmccurry]

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)

 

[Luke M]

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.

 

[Herbert Seidenberg]

Excel 2007
PivotTable, Table, GetPivotData,
three more ways.
http://www.mediafire.com/file/zwgonjmjz0g/04_18_09.xlsx