SOLVED

Sum total sold for each week

Brass Contributor

Hi,

 

Sheet1

Q5:Q1000 contains a value of between 1 and 52. The numbers represent week numbers.

O5:O1000 contains a value which is the quantity of a given item.

 

Sheet2

D5:D56 contain the values 1 to 52 which represents week numbers.

 

I need a formula that will be used in Sheet2 F5:F56 which will match the week number in Sheet2 D5:D56 and the corresponding week number in Sheet1 Q5:Q1000 and return the sum total of Sheet1 O5:O1000

 

For example, Sheet2 D18 contains the week no. 14

The formula then looks to find each instance of week 14 in Sheet1 Q5:Q1000 and then it should find the values in Sheet1 O5:O1000 and return the total amount for that week.

 

The formula should have found 3 instances where 14 can be found in Sheet1 Q5:Q1000

In Sheet1 O5:O1000 there should be 3 amounts to sum: 106, 254 and 198

In this example the formula should return 106 + 254 + 198 = 558

 

Sheet2

D18 week 14 the amount returned for that week is 558

 

Thank you for taking an interest!

 

1 Reply
best response confirmed by packie (Brass Contributor)
Solution

@packie 

In F5 on Sheet2:

 

=SUMIFS('Sheet1'!$O$5:$O$1000, 'Sheet1'!$Q$5:$Q$1000, D5)

 

Replace Sheet1 with the real name of that sheet, if necessary.

Fill down to F56.

1 best response

Accepted Solutions
best response confirmed by packie (Brass Contributor)
Solution

@packie 

In F5 on Sheet2:

 

=SUMIFS('Sheet1'!$O$5:$O$1000, 'Sheet1'!$Q$5:$Q$1000, D5)

 

Replace Sheet1 with the real name of that sheet, if necessary.

Fill down to F56.

View solution in original post