Sum Values in Matrix

Jorge Licea

Hello,

I'm trying to sum all the values in a 2 axis matrix that meet a criteria for row and column.

Name on rows and columns could be repeated.

In the example of the image the result be 11.

Thanks for your help.

Bassam Khalil

Hi @Jorge Licea

Hope you are fine, i confirm what @SK says in his excellent answer and i advice you to change your intake sheet format to be as following and use for sum the following formula

=SUMIFS([w1]:[w1], Employee:Employee, Criteria2, W:W, Criteria1) + SUMIFS([w2]:[w2], Employee:Employee, Criteria2, W:W, Criteria1) + SUMIFS([w3]:[w3], Employee:Employee, Criteria2, W:W, Criteria1)

here is a link to a sample sheet i prepare for you to test and change the criteria and see the result in Sum

Sameer Karkhanis

All I can say is that, you cannot specify range argument as a matrix (rows and columns), COLLECT expects the range as a column range.

Bassam Khalil

Hi @Jorge Licea

Hope you are fine, i confirm what @SK says in his excellent answer and i advice you to change your intake sheet format to be as following and use for sum the following formula

=SUMIFS([w1]:[w1], Employee:Employee, Criteria2, W:W, Criteria1) + SUMIFS([w2]:[w2], Employee:Employee, Criteria2, W:W, Criteria1) + SUMIFS([w3]:[w3], Employee:Employee, Criteria2, W:W, Criteria1)

here is a link to a sample sheet i prepare for you to test and change the criteria and see the result in Sum

Jorge Licea

Thank you @SK and @Bassam.M Khalil. I appreciate your help.