SUmproduct for multiple columns


I am looking for a formula that combines sumproduct and I think offset.

With sumproduct I identify a specific cell and I would like to sum the following N columns.

My current formula is: =SUMPRODUCT((AH!$D$4:$D$15=Sheet1!$B$6)*(AH!$E$2:$BD$2=Sheet1!$G$6)*(AH!$B$4:$B$15=Sheet1!$D$6)*(AH!$E$4:$BD$15)) but I now want it to add the following n columns.

I tried with an offset: =SUMPRODUCT((AH!$D$4:$D$15=$B$5)*(AH!$E$2:$BD$2=$G$5)*(AH!$B$4:$B$15=$D$5)*AVERAGE(OFFSET(AH!$E$4:$BD$15,,MATCH($G$5,AH!$E$2:$BD$2,0),1,$H$1))))

But this gives an offset width from the first cell in the data range.

By: Eric

Leave a Reply

Your email address will not be published. Required fields are marked *