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.

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]JAN-FFF[/TD]
[TD]JAN-HHH[/TD]
[TD]FEB-FFF[/TD]
[TD]FEB-HHH[/TD]
[TD]MAR-FFF[/TD]
[TD]MAR-HHH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]555[/TD]
[TD]0.5[/TD]
[TD]80[/TD]
[TD]0.5[/TD]
[TD]160[/TD]
[TD]0.5[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Zack[/TD]
[TD]555[/TD]
[TD]0.8[/TD]
[TD]40[/TD]
[TD]0.7[/TD]
[TD]90[/TD]
[TD]0.3[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]900[/TD]
[TD]0.1[/TD]
[TD]44[/TD]
[TD]0.9[/TD]
[TD]22[/TD]
[TD]0.1[/TD]
[TD]94[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John[/TD]
[TD]555[/TD]
[TD]0.1[/TD]
[TD]33[/TD]
[TD]0.75[/TD]
[TD]33[/TD]
[TD]1.0[/TD]
[TD]88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mike[/TD]
[TD]811[/TD]
[TD]0.3[/TD]
[TD]55[/TD]
[TD]0.9[/TD]
[TD]88[/TD]
[TD]0.3[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]April[/TD]
[TD]600[/TD]
[TD]0.4[/TD]
[TD]33[/TD]
[TD]0.4[/TD]
[TD]22[/TD]
[TD]0.1[/TD]
[TD]88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mike[/TD]
[TD]811[/TD]
[TD]0.2[/TD]
[TD]88[/TD]
[TD]0.9[/TD]
[TD]33[/TD]
[TD]0.2[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="width: 1105"]
<colgroup><col span="13"><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]JAN-FFF[/TD]
[TD]JAN-HHH[/TD]
[TD]FEB-FFF[/TD]
[TD]FEB-HHH[/TD]
[TD]MAR-FFF[/TD]
[TD="colspan: 2"]MAR-HHH[/TD]
[TD="align: right"]1[/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]JAN-FFF[/TD]
[TD]FEB-FFF[/TD]
[TD="colspan: 2"]MAR-FFF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]John[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zack[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]Zack[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]94[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]John[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]88[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]John[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]Mike[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]88[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]April[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]Mike[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 14"]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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 14"]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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 14"]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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]by using offset function you can automatically split the the table into FFF and HHH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JAN-FFF[/TD]
[TD]FEB-FFF[/TD]
[TD]MAR-FFF[/TD]
[TD]Data[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 8"]then it is cery easy and staightforward to use sumproduct to sum by John and 555[/TD]
[TD][/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]1[/TD]
[TD]Sum of JAN-FFF[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sum of FEB-FFF[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sum of MAR-FFF[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 3"]0.75 Sum of JAN-FFF[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 3"]0.75 Sum of FEB-FFF[/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 3"]0.75 Sum of MAR-FFF[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]0.1 Sum of JAN-FFF[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]0.1 Sum of FEB-FFF[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]0.1 Sum of MAR-FFF[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD]Sum of JAN-FFF[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=SUMPRODUCT(($K$3:$K$9=C26)*($L$3:$L$9=C27)*($M$3:$M$9))[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sum of FEB-FFF[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Sum of MAR-FFF[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"]0.5 Sum of JAN-FFF[/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]this just for Jan but add elements with n3:n9 etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"]0.5 Sum of FEB-FFF[/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 3"]0.5 Sum of MAR-FFF[/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]or a pivot table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]0.5 Sum of JAN-FFF[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"](can be tidied up)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]0.5 Sum of FEB-FFF[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]0.5 Sum of MAR-FFF[/TD]
[TD] [/TD]
[TD="align: right"]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]bottom 3 rows are the totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Sum of JAN-FFF[/TD]
[TD] [/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Sum of FEB-FFF[/TD]
[TD] [/TD]
[TD="align: right"]1.25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Total Sum of MAR-FFF[/TD]
[TD] [/TD]
[TD="align: right"]1.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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