sumproduct to moves with column

[ALAMZEB]

Hi


I get data from my supplier for every next 13 weeks. I am using Sum product formula to calculate sum of each pack from table.

The formula first find value from list in column A (packs ) than weekly dates than sum them,

Because data changes every weeks so I want columns in formula to move with weekly dates


In example attached


The result 200 is coming from column B but if I get different date next week, the column will not move.

 

[Nebu]

Hi:

Find the attached.

=IFERROR(SUMPRODUCT((($A$3:$A$12=$A$20)*($B$1:$N$1=B19)*(OFFSET(INDIRECT(ADDRESS(1,MATCH(B$19,$A$1:$N$1,0))),2,,COUNTA($A$3:$A$12),1)))),0)

Thanks

 

[r1c1]

Interesting question and cool answer @Nebu

Here is one more variant.

=SUMPRODUCT( MMULT(($A$3:$A$12=$A20)*1,($B$1:$N$1=J$19)*1),$B$3:$N$12 )

This one uses MMULT to generate a 2D array of 0s & 1s, 1 where the corresponding values in B3:N12 should be added up and 0 for values to be omitted. We then use SUMPRODUCT to just get the total.

 

[ALAMZEB]

Thanks guys

both solution are brilliant

 

[Hui]

Your original formula was oh so close!

I would use:
=SUMPRODUCT((($A$3:$A$12=$A$20)*($B$1:$N$1=K19)*($B$3:$N$12)))
which is only marginally different (in red) to your original formula

This technique is discussed here:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/