CODEWITHSUNDEEP
excelmatrix
André Svalland
André Svalland

Reputation: 11

Excel summing with multiple criteria in matrix

First time user, so please excuse my noviceness...

I'm wondering which formula to apply in Excel when the aim is to sum figures in a matrix where there are multiple criterias.

Example matrix: Example matrix

Please see the above link for matrix. I want to sum the cells with "2017" and "a" as criteria in one single cell (hence the result should be 76 (15+16+15+30)).

Thanks in advance.

Upvotes: 1

Views: 2051

Answers (2)

Васил Недялков
Васил Недялков

Reputation: 1

I have the same issue but instead to look for a fixed criteria (in your case 2017) I need to search for a partial text in the range B1:E1

Example:

  • Search Word will be "Turkey"
  • Cell contains: "Turkey+Promotional" or "Turkey" or "Turkey + Advertising"
  • In all cases it should start with Turkey as first word

Also I need to have a fixed range with "OFFSET" function since I need to keep the formula evaluating the same range because some columns are deleted in the work process and new are added at the back of the list.

My working solution is almost the same Example:

{=SUM((OFFSET(Sheet2!$E$6;1;1;171;40))*(--(Sheet2!$C$7:$C$177=B296))*(--(OFFSET(Sheet2!$E$1;0;1;1;40)="TR")))}

My goal is to change the last part (OFFSET(Sheet2!$E$1;0;1;1;40)="TR") to something that evaluate the content of the cell. Tried with IF(Countif() but I couldn't made it work.

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

If your original data is in B2:E4 with headers in the first row and column, and your criteria in H2:L2 with headers in the first row and column G, it should be

=SUMPRODUCT($B$2:$E$4*($B$1:$E$1=H$1)*($A$2:$A$4=$G2))

starting in H2 and pulled across and down.

enter image description here

Upvotes: 2

Related Questions