Forum Discussion

packie's avatar
packie
Brass Contributor
Apr 12, 2024

Sum total sold for each week

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!

 

  • 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.

  • 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.

Resources