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 fa...
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.