Search code examples
excelindexingmatchsumifs

Sumifs or index match?

Thank you for helping me out. I am working on a way to add up how many pieces each department is handling per week...My data are organized as below. Each row represents an order. There is an order qty. And the orange grid has the week numbers during which the department is handling that order.

Data layout

enter image description here

I am trying to summarize by week how many pieces each department is handling..and While I have a series of sumif statements to work around, I am wondering if the brain trust has a more elegant solution...Thanks again.

desired output

enter image description here

Solution

  • MMULT() with SUMPRODUCT() may give you expected result.

    =SUMPRODUCT(MMULT(($C$2:$G$4=$A8)*($C$1:$G$1=B$7),TRANSPOSE({1,1,1,1,1}))*($B$2:$B$4))

    You may need to enter the formula as array entry means confirm with CTRL+SHIFT+ENTER. If you have Excel O365 then you can use dynamic formula SEQUENCE() which will give you best performance for large numbers of columns. Try-

    =SUMPRODUCT(MMULT(($C$2:$G$4=$A8)*($C$1:$G$1=B$7),SEQUENCE(COLUMNS($C$1:$G$1),,,0))*($B$2:$B$4))

    enter image description here