Forum Discussion

BFK's avatar
BFK
Copper Contributor
Jul 02, 2025

Sumifs or other solution for both vertical & horizontal criteria

Hi all, I would like to sum values from a matrix of vertical critiera (e.g. names) and horizontal ones (dates as well as other criteria). I am not sure the sumifs formula can actually do this. So fa...
excel
Formulas and Functions
ExcelAnalytica's avatar
ExcelAnalytica
Copper Contributor
Jul 07, 2025

If I've read your request correctly:
— Your data is structured like the matrix in A1:F6, and will contain multiple entries
— You would like a formula to standardize the matrix data into a 4 Column "Table"

 

The formula to do this is as array formula, so when you enter it into your cell, you'll need to hit CTRL + SHIFT + ENTER.  The curly braces around the formula indicate the formula is being calculated as an array formula.

 

{=SUM(IF($B$1:$G$1&$B$2:$G$2=$J1&$K1, IF($A$3:$A$7 = $L1, $B$3:$G$7)))}

 

The nested IFs will return an array where only the values that fit the criteria come through, everything else is FALSE.

Example: 

{94,15,FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

 

The Sum Function then sums the array.

 

Once entered as an array formula, you can copy and paste the formula normally and pasted formulas will retain the array status (you'll see the {curly braces}).  

 

 

Resources