SUMIFS Help

[Help2022]

Trying to compile data from across a large data set and SUMIFS seems to be failing - I don't know if it can sum if there are columns and rows being searched.

I created a simplified version of the data set and expected out come in the image below.

Data tab
Column A, rows 3-5 = Datapoints
Columns B - F, row 1 = Category groupings

On my summary, I want to be able to sum selected datapoints across the category groupings.

I tried using the following "=SUMIFS($B$3:$F$5,$A$3:$A$5,$A10,$B$1:$F$1,B$9)" but keep having errors.

Hope someone can point me in the right direction - either a fix to my formula or a better formula to use.

 

[Help2022]

Solved my own issue - =SUMPRODUCT(($B$3:$F$5)*($A$3:$A$5=$A10)*($B$1:$F$1=B$9))

Leaving this here incase anyone searches for a similar issue.

 

[6StringJazzer]

In SUMIFS all the ranges must have the same shape. You are mixing rows and columns so it doesn't work.

Your data format is working against you. If your data looked like this you could just create a pivot table to do it automatically:
Profit Category 1 4
Profit Category 2 1
Profit Category 3 1
Profit Category 1 1