Sum a range of cells

[NotTheBest]

Hello folks

I'm sure this is very easy but I am struggling with it because of multiple rows...

Column B has a list of names (e.g. B8:B12 = Alpha, B13:B17 = Beta etc)
Row 10 has years (e.g. D10:F11 = 2021, G11:M11 = 2022, etc)
Below each year and to the right of each name are values

I'm trying to write a formula to calculate the total value for each name for each year

2021 2022 2023
Alpha $?
Beta $?

Kindly help me if possible. Or let me know if this is unclear and I will try to explain better.

[NotTheBest]

I have attached my file to explain, I tried writing a formula but it didn't work

[davsth]

=SUMPRODUCT(($D$13:$X$20)*($B$13:$B$20=$D31)*($D$10:$X$10=E$30)) it works in similar ways to the sumifs each condition in the bracket results in a true or false, multiple together they become 1s and 0s which get multiplied by you sum values to get the answer you are looking for

the below link might give you a fuller explanation with a worked example moving up to rows and columns

https://gorilla.bi/excel/sumproduct-multiple-criteria/

[Kaper]

Try:

=SUMPRODUCT($D$13:$X$20,($D$10:$X$10=E$30)*($B$13:$B$20=$D31))

Edit: Whoops have not seen the previous answer.

[NotTheBest]

thank you both that's great! I'm glad I asked as I would not have worked that out very easily, I was determined to use sumif/sumifs

i was trying to use index match as well (unsuccessfully), would this type of formula work in these situations as well or was I barking up the wrong tree again

[wk9128]

thank you both that's great! I'm glad I asked as I would not have worked that out very easily, I was determined to use sumif/sumifs

i was trying to use index match as well (unsuccessfully), would this type of formula work in these situations as well or was I barking up the wrong tree again

SUMPRODUCT formula is much simpler, SUMIF formula is much more complicated to write, please refer to
Cell E25 array formula , Drag down and across

[davsth]

If you had wanted one value of a range of values, index match, or offset match would have worked well to return the value. As you were wishing to sum the values this was a better approach

[NotTheBest]

Thanks both for your further explanations

[wk9128]

You're Welcome. Thank You for the feedback