conditional summing of data from another worksheet
 

I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435

conditional summing of data from another worksheet
 

Try this formula in Cell E1 of Worksheet 2. Copy down as needed.

=SUMPRODUCT(--('Worksheet 1'!$B$1:$B$5=B1),'Worksheet 1'!$F$1:$F$5)

Adjust the ranges to meet your needs.

HTH,
Elkar


"Tom" wrote:

I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435

conditional summing of data from another worksheet
 

With the week numbers in column B of Sheet2 (assume starting in B2),
then put this formula in E2:

=SUMIF(Sheet1!B:B,B2,Sheet1!F:F)

then copy this down for as many numbers as you have in column B.

Hope this helps.

Pete

On Nov 6, 7:05 pm, Tom wrote:
I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435

conditional summing of data from another worksheet
 

Good help Pete, one more part to that. B2 in that formula should change by an
increment of 1 when the formula is copied down. for example, B2 starts as 11,
as the formula is copied down, B2 should then change to 12, 13, 14, etc. Any
thoughts?

"Pete_UK" wrote:

With the week numbers in column B of Sheet2 (assume starting in B2),
then put this formula in E2:

=SUMIF(Sheet1!B:B,B2,Sheet1!F:F)

then copy this down for as many numbers as you have in column B.

Hope this helps.

Pete

On Nov 6, 7:05 pm, Tom wrote:
I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435

conditional summing of data from another worksheet
 

nevermind i figured out my mini error but thanks for the formula pete, worked
great!!!

"Tom" wrote:

Good help Pete, one more part to that. B2 in that formula should change by an
increment of 1 when the formula is copied down. for example, B2 starts as 11,
as the formula is copied down, B2 should then change to 12, 13, 14, etc. Any
thoughts?

"Pete_UK" wrote:

With the week numbers in column B of Sheet2 (assume starting in B2),
then put this formula in E2:

=SUMIF(Sheet1!B:B,B2,Sheet1!F:F)

then copy this down for as many numbers as you have in column B.

Hope this helps.

Pete

On Nov 6, 7:05 pm, Tom wrote:
I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435

conditional summing of data from another worksheet
 

You're welcome, Tom - glad to hear it worked for you.

Pete

On Nov 6, 8:29 pm, Tom wrote:
nevermind i figured out my mini error but thanks for the formula pete, worked
great!!!



"Tom" wrote:
Good help Pete, one more part to that. B2 in that formula should change by an
increment of 1 when the formula is copied down. for example, B2 starts as 11,
as the formula is copied down, B2 should then change to 12, 13, 14, etc. Any
thoughts?

"Pete_UK" wrote:

With the week numbers in column B of Sheet2 (assume starting in B2),
then put this formula in E2:

=SUMIF(Sheet1!B:B,B2,Sheet1!F:F)

then copy this down for as many numbers as you have in column B.

Hope this helps.

Pete

On Nov 6, 7:05 pm, Tom wrote:
I am looking for a formula to take data from worksheet 1 column F based on
week number in column B and sum it in worksheet 2 column E. catch is that
there are a different number of entries for each week number in column B.

Example: worksheet 1

Column B Column F

11 120
11 156
11 98
12 256
12 179

worksheet 2
Column B Column E

11
should be 374
12
should be 435- Hide quoted text -

- Show quoted text -