When theres more than one of the same value in one column, what formula
would I need to use to add data associated with that value in corresponding
columns and display the results on a separate worksheet. The number of
instances can vary, so next time, Joe may have 3 instances, Sam 4.....

So that this on one worksheet:
resource hours

Joe 5
Joe 8
Sam 17
Peter 2
Peter 7
Peter 23

Results in the following on another worksheet
resource hours

Joe 13
Sam 17
Peter 32

Your help is appreciated

Ideally a pivot table, but if you don't want to use this then
SUMIF(range,criteria,sum range)

But a pivot table is the best solution, as you won't need to maintain
the names on the second sheet, just refresh the pivot table


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=529710

=SUMPRODUCT(--(Sheet1!A1:A6="JOE"),--(Sheet1!B1:B6)) assuming this is not on
Sheet1

If you have your names in a column in the second worksheet, then you could
use:

=SUMPRODUCT(--(Sheet1!A1:A6=A1),--(Sheet1!B1:B6))

where A1 in Sheet2 (for example) contains JOE, A2 SAM etc with formula in
B1,B2 etc

HTH

"C-A" wrote:

When theres more than one of the same value in one column, what formula
would I need to use to add data associated with that value in corresponding
columns and display the results on a separate worksheet. The number of
instances can vary, so next time, Joe may have 3 instances, Sam 4.....

So that this on one worksheet:
resource hours

Joe 5
Joe 8
Sam 17
Peter 2
Peter 7
Peter 23

Results in the following on another worksheet
resource hours

Joe 13
Sam 17
Peter 32

Your help is appreciated

Thanks for your quick replies!

How about if I need to add across columns too? i.e.

week 1 week 2 week 3 week 4
Joe 40 20 17 20
Joe 20 3 20
Sam 17 40 40 40
Peter 2 2 10 2
Peter 7 2 20 2
Peter 23 2 10 2

results in:

Joe 40 40 20 40
Sam 17 40 40 40
Peter 32 6 40 6


Thanks again,
C-A



"Toppers" wrote:

=SUMPRODUCT(--(Sheet1!A1:A6="JOE"),--(Sheet1!B1:B6)) assuming this is not on
Sheet1

If you have your names in a column in the second worksheet, then you could
use:

=SUMPRODUCT(--(Sheet1!A1:A6=A1),--(Sheet1!B1:B6))

where A1 in Sheet2 (for example) contains JOE, A2 SAM etc with formula in
B1,B2 etc

HTH

"C-A" wrote:

When theres more than one of the same value in one column, what formula
would I need to use to add data associated with that value in corresponding
columns and display the results on a separate worksheet. The number of
instances can vary, so next time, Joe may have 3 instances, Sam 4.....

So that this on one worksheet:
resource hours

Joe 5
Joe 8
Sam 17
Peter 2
Peter 7
Peter 23

Results in the following on another worksheet
resource hours

Joe 13
Sam 17
Peter 32

Your help is appreciated

If Week 1 is column B, Week2 column C then change B to C etc

=SUMPRODUCT(--(Sheet1!A1:A6=A1),--(Sheet1!B1:B6))


"C-A" wrote:

Thanks for your quick replies!

How about if I need to add across columns too? i.e.

week 1 week 2 week 3 week 4
Joe 40 20 17 20
Joe 20 3 20
Sam 17 40 40 40
Peter 2 2 10 2
Peter 7 2 20 2
Peter 23 2 10 2

results in:

Joe 40 40 20 40
Sam 17 40 40 40
Peter 32 6 40 6


Thanks again,
C-A



"Toppers" wrote:

=SUMPRODUCT(--(Sheet1!A1:A6="JOE"),--(Sheet1!B1:B6)) assuming this is not on
Sheet1

If you have your names in a column in the second worksheet, then you could
use:

=SUMPRODUCT(--(Sheet1!A1:A6=A1),--(Sheet1!B1:B6))

where A1 in Sheet2 (for example) contains JOE, A2 SAM etc with formula in
B1,B2 etc

HTH

"C-A" wrote:

When theres more than one of the same value in one column, what formula
would I need to use to add data associated with that value in corresponding
columns and display the results on a separate worksheet. The number of
instances can vary, so next time, Joe may have 3 instances, Sam 4.....

So that this on one worksheet:
resource hours

Joe 5
Joe 8
Sam 17
Peter 2
Peter 7
Peter 23

Results in the following on another worksheet
resource hours

Joe 13
Sam 17
Peter 32

Your help is appreciated