conditional summing of data from another worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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?
 
nevermind i figured out my mini error but thanks for the formula pete, worked
great!!!
 
Back
Top