Forum Discussion
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 far I am getting errors. Hence, I would be very grateful if someone had another simple way/ formula to have the results calculated.
Example is as such:
Criteria 1 (vertical): Names
Criteria 2 (horizontal): Date (1st row)
Criteria 3 (horizontal): "Plan" (only) i.e. (2nd row)
Sum-up values: In the empty cells in the matrix of the 3 criteria
| 1st May 25 | 1st Jun 25 | 1st Jul 25 | 1st Aug 25 | 1st Sep 25 | |
| Actual | Actual | Plan | Plan | Plan | |
| Name 1 | |||||
| Name 2 | |||||
| Name 2 | |||||
| Name 3 |
13 Replies
Apologies if this posts twice, I just posted a few minutes ago and didn't see it here.
If I've read your question properly:
-- Your data is in the matrix A1:G7
-- You would like to standardize the data into a 4-column table.Using the following Array formula will do this.
{=SUM(IF($B$1:$G$1&$B$2:$G$2=$J1&$K1, IF($A$3:$A$7 = $L1, $B$3:$G$7)))}
When entering the array formula you'll need to use CTRL+SHIFT+ENTER and you'll see the {curly braces} that indicates Excel is calculating using Array.
Dear ExcelAnalytica its actually the other way round - sorry for the misleading explanation.
A-G is actually the target structure and current structure is such:
Jun Jul Jul
Actual Actual PlanName 1 5 1 2
Name 1 5 2 1
Name 2 3 2 1
Name 1 2 1 1
Name 3 2 1 3
.....
I.e. I have multiple mentioning of the "name" and simply want to sum by name otherwise same structure
If only names to summarise that could be
=LET( range, $B$2:$H$7, data, DROP(range,2), names, TAKE(data,,1), values, DROP(data,,1), result, VSTACK( TAKE(range, 2), GROUPBY(names, values, SUM,0,0) ), IF(result = "", "", result) )
Apologies if this posts twice, I just posted a few minutes ago and didn't see it here.
If I've read your question properly:
-- Your data is in the matrix A1:G7
-- You would like to standardize the data into a 4-column table.Using the following Array formula will do this.
{=SUM(IF($B$1:$G$1&$B$2:$G$2=$J1&$K1, IF($A$3:$A$7 = $L1, $B$3:$G$7)))}
When entering the array formula you'll need to use CTRL+SHIFT+ENTER and you'll see the {curly braces} that indicates Excel is calculating using Array.
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}).
=SUMIFS($L$1:$L$22,$K$1:$K$22,$A3,$J$1:$J$22,B$2,$I$1:$I$22,B$1)This formula is in cell B3 and filled across range B3:F6 in my sample sheet.
Thanks Oliver for your explanation. I realised, I missed to explain, that the data is currently in the matrix and I want it in the following structure, but summed up, since currently (also in the matrix, there is double mentioning of names and dates possible (for the latter I hence also have the "plan"):
Jun 25 Jul 25 ......
Name 1
Name 2
Name 3
Alternatively you can use the relatively new PIVOTBY()
=PIVOTBY(K1:K22,I1:J22,L1:L22,SUM,,0,,0)see attached
Thanks M for your explanation. I realised, I missed to explain, that the data is currently in the matrix and I want it in the following structure, but summed up, since currently (also in the matrix, there is double mentioning of names and dates possible (for the latter I hence also have the "plan"):
Jun 25 Jul 25 ......
Name 1
Name 2
Name 3
