Sum multiple columns based on header

I have an excel file at which Im attempting to do a sum for all headers for a specific criteria.
I was successful at creating a formula that allows to find multiple criteria's and sum a single header, however, Im struggling to
create a formula that would allow me to sum multiple headers for that criteria without creating 20-30 sumifs and combine them.
Much easier to have a list of headers like I did in the first example where users can change the criterias as needed. Trying to do the same for the headers.

Any thoughts?

More info explained:
In my example, the Green 8 is correct because its summing the Header (H2) and summing all criteria's (a, b).
Users have the ability to adjust the criteria (remove a, add c, etc..). Users have the ability to replace Header H2 to change it to H3 which will give you 12

In the yellow highlights,
I should get a count of 21 since your adding all criteria's (a) with headers (H2, H1, H4).
Users should be able to modify the criteria and headerse.g., add c, remove a, Add H3) which in this example should then give them 20

Welcome to the forum.

Explain why 8 is correct for the green formula. Tell us what value you are expecting for the yellow formula (value, not description).

In my example, the Green 8 is correct because its summing finding the Header (H2) and summing all criteria's (a,b).

But that's 4, not 8 ...

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G	H	I	J	K
1		H1	H2	H3	H4				group to sum	Header to sum	Formula
2	a	1	2	3	4				a	H2	8
3	b	1	2	3	4				b		2+2=4

Sheet: Sheet1

I should get a count of 21 since your adding all criteria's (a) with headers (H2, H1, H4).

I make that one 8. Don't understand - sorry.

In your table, your missing rows 5 and 6 which also contain an the criteria: "a" and H2 has a value of 2 of each of those rows

screenshot.JPG

Wow! Thanks, I do like that this is a lot cleaner and would love to use this for future examples .
After testing the S2 formula I ran into two errors:
-Users should be able to add more criteria's since its not always going to be one criteria. e.g., they should be able to b, c, d, etc...

-The header group seems to be locked and has to be filled. in other words, less advanced users wont understand that the data in Column R cannot be blank.
Im trying to do it where if I put Range R1:R5, it would sum all values in that range whether I have 5 values in there or only 1.

Originally Posted by MavMasterExcel

In your table, your missing rows 5 and 6 which also contain an the criteria: "a" and H2 has a value of 2 of each of those rows

Attachment 833208

Doh!!!

I'll go back to the gardening ...