Sum Values in Matrix

Jorge Licea · 01/29/21

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.

2021-01-28 20_18_37-New Sheet - Smartsheet.com.jpg

Bassam Khalil · 02/04/21

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 · 01/29/21

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 · 02/04/21

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)