Calculate value from criteria in row and column title

Question

I got problem in excel. I have this source table

And i want to fill below table with the sum value based on criteria on related column & row.

Please help. Using sumif is so much work, because i have many tables that i need to fill. I believe there will be much better way than that.

I dont know whether an array formula by using MATCH, INDEX & INDIRECT could solve this.

Based on value on each cell header (row and column). For example, in cell #1 in the result table, the criteria will be 'Def' and '2016'. — y2a
Thanks pnuts. Are there any other options? Like an array INDEX, MATCH & INDIRECT solutions? — y2a

Glitch_Doctor Glitch_Doctor · Accepted Answer · 2017-08-04T08:37:50

THIS IS AN ARRAY FORMULA -

WHILE STILL IN THE FORMULA BAR USE Ctrl+Shift+Enter

{=SUM(IF($A$3:$A$7=B$9,IF($B$2:$K$2=$A10,$B$3:$K$7)))}

As this is an array formula, the IF statements build separate arrays of TRUE/FALSE and assess the output against the array of values, returning either FALSE or the value as another array... Sum is then totalling this array and as it only works for true values it is ignoring the FALSE outputs in the array:

=SUM(IF({TRUE;FALSE;FALSE;FALSE;FALSE},
IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE},
$B$3:$K$7)))

=SUM({0,FALSE,0,FALSE,0,FALSE,119.6,FALSE,51.4,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,FALSE,FALSE,FALSE;
FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE})

Notice how the array recognises each row and assigns a semi-colon as opposed to a comma.

Calculate value from criteria in row and column title

1 Answers