add multiple entries and display on another sheet

[C-A]

When there’s 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

[Gary Brown]

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

[Toppers]

=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 there’s 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

[C-A]

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 there’s 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

[Toppers]

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 there’s 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