Forum Discussion
Sumifs or other solution for both vertical & horizontal criteria
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 Plan
Name 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) )
