Sum Multiple Columns in Table based string in Table Headers

[bbjok]

Need serious help.
I have a spreadsheet with multiple columns in a table with the same 3 digit ending.
Table Header names: Name, ID, JAN-FFF, JAN-HHH, FEB-FFF, FEB-HHH, MAR-FFF, MAR-HHH, and so on until DEC-HHH

The -FFF and -HHH Columns are all numbers but represent different aspects.
Using a Formula in cell I1.
Given the table with data is named tblNAMEDRANGE is a Named Range "NAMEDRANGE"
I want to sum all columns that end with header ending -FFF and have "John" in the Name Column and "555" in the ID Column.
I want to sum all columns that end with header ending -HHH and have "John" in the Name Column and "555" in the ID Column.
I want to sum all columns that end with header ending -FFF and have "Mike" in the Name Column and "811" in the ID Column.

	A	B	C	D	E	F	G	H	I
1	Name	ID	JAN-FFF	JAN-HHH	FEB-FFF	FEB-HHH	MAR-FFF	MAR-HHH
2	John	555	0.5	80	0.5	160	0.5	200
3	Zack	555	0.8	40	0.7	90	0.3	12
4	John	900	0.1	44	0.9	22	0.1	94
5	John	555	0.1	33	0.75	33	1.0	88
6	Mike	811	0.3	55	0.9	88	0.3	11
7	April	600	0.4	33	0.4	22	0.1	88
8	Mike	811	0.2	88	0.9	33	0.2	33
9

<tbody>
</tbody>

 

[XLearner]

Hi,

=SUMPRODUCT( (RIGHT(C1:H1,4)="-FFF")*(A2:A8="John")*(B2:B8=555)*(C2:H8) )
=SUMPRODUCT( (RIGHT(C1:H1,4)="-HHH")*(A2:A8="John")*(B2:B8=555)*(C2:H8) )
=SUMPRODUCT( (RIGHT(C1:H1,4)="-FFF")*(A2:A8="Mike")*(B2:B8=811)*(C2:H8) )

Regards
XLearner

 

[oldbrewer]

												2	4	6
Name	ID	JAN-FFF	JAN-HHH	FEB-FFF	FEB-HHH	MAR-FFF	MAR-HHH	1	Name	ID	JAN-FFF	FEB-FFF	MAR-FFF
John	555	0.5	80	0.5	160	0.5	200		2	John	555	0.5	0.5	0.5
Zack	555	0.8	40	0.7	90	0.3	12		3	Zack	555	0.8	0.7	0.3
John	900	0.1	44	0.9	22	0.1	94		4	John	900	0.1	0.9	0.1
John	555	0.1	33	0.75	33	1	88		5	John	555	0.1	0.75	1
Mike	811	0.3	55	0.9	88	0.3	11		6	Mike	811	0.3	0.9	0.3
April	600	0.4	33	0.4	22	0.1	88		7	April	600	0.4	0.4	0.1
Mike	811	0.2	88	0.9	33	0.2	33		8	Mike	811	0.2	0.9	0.2
I want to sum all columns that end with header ending -FFF and have "John" in the Name Column and "555" in the ID Column.
I want to sum all columns that end with header ending -HHH and have "John" in the Name Column and "555" in the ID Column.
I want to sum all columns that end with header ending -FFF and have "Mike" in the Name Column and "811" in the ID Column.
										Name	John
										ID	555
	by using offset function you can automatically split the the table into FFF and HHH
										JAN-FFF	FEB-FFF	MAR-FFF	Data	Total
	then it is cery easy and staightforward to use sumproduct to sum by John and 555		0.1	0.75	1	Sum of JAN-FFF	0.1
													Sum of FEB-FFF	0.75
													Sum of MAR-FFF	1
											0.75 Sum of JAN-FFF	0.1
		John									0.75 Sum of FEB-FFF	0.75
		555									0.75 Sum of MAR-FFF	1
										0.1 Sum of JAN-FFF			0.1
										0.1 Sum of FEB-FFF			0.75
		0.6								0.1 Sum of MAR-FFF		1
										0.5	0.5	0.5	Sum of JAN-FFF	0.5
		=SUMPRODUCT(($K$3:$K$9=C26)*($L$3:$L$9=C27)*($M$3:$M$9))					Sum of FEB-FFF	0.5
													Sum of MAR-FFF	0.5
											0.5 Sum of JAN-FFF		0.5
		this just for Jan but add elements with n3:n9 etc					0.5 Sum of FEB-FFF		0.5
											0.5 Sum of MAR-FFF	0.5
		or a pivot table							0.5 Sum of JAN-FFF			0.5
		(can be tidied up)							0.5 Sum of FEB-FFF			0.5
										0.5 Sum of MAR-FFF		0.5
		bottom 3 rows are the totals						Total Sum of JAN-FFF		0.6
										Total Sum of FEB-FFF		1.25
										Total Sum of MAR-FFF		1.5

<colgroup><col span="13"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[bbjok]

Hi,

=SUMPRODUCT( (RIGHT(C1:H1,4)="-FFF")*(A2:A8="John")*(B2:B8=555)*(C2:H8) )
=SUMPRODUCT( (RIGHT(C1:H1,4)="-HHH")*(A2:A8="John")*(B2:B8=555)*(C2:H8) )
=SUMPRODUCT( (RIGHT(C1:H1,4)="-FFF")*(A2:A8="Mike")*(B2:B8=811)*(C2:H8) )

Regards
XLearner

PERFECT!!!!!

Thank you very very much that works.

My new query is =SUMPRODUCT((RIGHT(tblNAMEDRANGE[[#Headers],[Feb-FFF]:[DEC-HHH]],4)="-FFF")*(tblNAMEDRANGE[ID]=$I$2)*(ValuesNAMEDRANGE))
ValuesNAMEDRANGE is C1:H1
$I$2 is the ID which is actually a Data Validation List of "ID" field to use as a filter criteria for the query.
I will also change ..."-FFF"... to a reference to a Data Validation List of filter criteria.

I have follow-on questions:
1. Can or can't I use ...RIGHT(A1:H1,4)...
2. Can or can't I just use ...RIGHT(tblNAMEDRANGE[#Headers],4)...
2a. I actually implemented it like this and it works: ...RIGHT(tblNAMEDRANGE[#Headers],[JAN-FFF]:[DEC-HHH]],4)...

 

[XLearner]

2a. I actually implemented it like this and it works: ...RIGHT(tblNAMEDRANGE[#Headers],[JAN-FFF]:[DEC-HHH]],4)...

Should work no problem