add multiple entries and display on another sheet
 

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

add multiple entries and display on another sheet
 

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

add multiple entries and display on another sheet
 

=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

add multiple entries and display on another sheet
 

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

add multiple entries and display on another sheet
 

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