Summarizing data in a table using Sumproduct Sumif Indirect with named ranges and table references

[number_cruncher]

I am using the table tblSummary below to sum data from other tables in the workbook. All table names are in a named range called nrSheetParameters. Some of the tables have additional columns between each month so [sum range] criteria needs to be the same column name in each table as the column the formula is in. My formula below is working and accurate in the January column, but when i copy it across to the other columns the data from the dat in the Jan column repeats.

FORMULA IN JAN COLUMN =SUMPRODUCT(SUMIF(INDIRECT(nrSheetParameters&"[PRODUCTS]"),tblSummary[@[PRODUCTS]:[PRODUCTS]],INDIRECT(nrSheetParameters&"["&UPPER(TEXT(MONTH(DATEVALUE(tblSummary[[#Headers],[JAN]] & "1")),"MMM"))&"]")))

FORMULA IN FEB COLUMN: =SUMPRODUCT(SUMIF(INDIRECT(nrSheetParameters&"[PRODUCTS]"),tblSummary[@[PRODUCTS]:[PRODUCTS]],INDIRECT(nrSheetParameters&"["&UPPER(TEXT(MONTH(DATEVALUE(tblSummary[[#Headers],[FEB]] & "1")),"MMM"))&"]")))

Why is this happening?? Thanks in advance for any help you can offer Cheers!

I am using Microsoft Excel for Mac v16.67

table name: tblSummary

PRODUCTS	JAN	FEB	MAR	APR	MAY	JUN	JUL
CRATES	510​	510​	510​	510​	510​	510​	510​
CRATE WHEELS	0​	0​	0​	0​	0​	0​	0​
4 WHEELERS	0​	0​	0​	0​	0​	0​	0​
PANEL CARTS	0​	0​	0​	0​	0​	0​	0​
SPEED PK	0​	0​	0​	0​	0​	0​	0​
E-CART	0​	0​	0​	0​	0​	0​	0​
LIBRARY CRT	0​	0​	0​	0​	0​	0​	0​
D- LIBRARY	720​	720​	720​	720​	720​	720​	720​
COOLERS	0​	0​	0​	0​	0​	0​	0​
J-BARS	0​	0​	0​	0​	0​	0​	0​
APPLIANCE DOLLY	0​	0​	0​	0​	0​	0​	0​
PIANO BOARDS	0​	0​	0​	0​	0​	0​	0​
SAFE JACKS (SETS)	0​	0​	0​	0​	0​	0​	0​
LADDERS	0​	0​	0​	0​	0​	0​	0​
KICK BACK DOLLY	0​	0​	0​	0​	0​	0​	0​
SCALE	0​	0​	0​	0​	0​	0​	0​
POLY NIGHT (FT)	0​	0​	0​	0​	0​	0​	0​
PALLET JACK	0​	0​	0​	0​	0​	0​	0​
LIFT TABLE	0​	0​	0​	0​	0​	0​	0​
SILVERWARE	0​	0​	0​	0​	0​	0​	0​

 

[Fluff]

How about

=SUMPRODUCT(SUMIF(INDIRECT(nrSheetParameters&"[PRODUCTS]"),tblSummary[@[PRODUCTS]:[PRODUCTS]],INDIRECT(nrSheetParameters&"["&tblSummary[[#Headers],[JAN]]&"]")))

 

[number_cruncher]

How about

=SUMPRODUCT(SUMIF(INDIRECT(nrSheetParameters&"[PRODUCTS]"),tblSummary[@[PRODUCTS]:[PRODUCTS]],INDIRECT(nrSheetParameters&"["&tblSummary[[#Headers],[JAN]]&"]")))

Thank you @Fluff! This worked like a charm! It looks like I over-thought and over-complicated it. The next issue I need to tackle is how to handle errors if any of the tables are missing any of the month columns (like you see in the Jun & Jul columns). Let me know if I need to post this question in a new thread.

table: tblSummary

PRODUCTS	JAN	FEB	MAR	APR	MAY	JUN	JUL
CRATES	510​	0​	100​	0​	0​	#REF!​	#REF!​
CRATE WHEELS	0​	25​	0​	0​	0​	#REF!​	#REF!​
4 WHEELERS	0​	750​	0​	0​	0​	#REF!​	#REF!​
PANEL CARTS	0​	0​	0​	0​	0​	#REF!​	#REF!​
SPEED PK	0​	0​	0​	0​	0​	#REF!​	#REF!​
E-CART	0​	0​	0​	0​	0​	#REF!​	#REF!​
LIBRARY CRT	0​	0​	0​	0​	0​	#REF!​	#REF!​
D- LIBRARY	720​	0​	200​	0​	0​	#REF!​	#REF!​
COOLERS	0​	0​	0​	0​	0​	#REF!​	#REF!​
J-BARS	0​	0​	0​	0​	0​	#REF!​	#REF!​
APPLIANCE DOLLY	0​	0​	0​	0​	0​	#REF!​	#REF!​
PIANO BOARDS	0​	0​	0​	0​	0​	#REF!​	#REF!​
SAFE JACKS (SETS)	0​	0​	0​	0​	0​	#REF!​	#REF!​
LADDERS	0​	0​	0​	0​	0​	#REF!​	#REF!​
KICK BACK DOLLY	0​	0​	0​	0​	0​	#REF!​	#REF!​
SCALE	0​	0​	0​	0​	0​	#REF!​	#REF!​
POLY NIGHT (FT)	0​	0​	0​	0​	0​	#REF!​	#REF!​
PALLET JACK	0​	0​	0​	0​	0​	#REF!​	#REF!​
LIFT TABLE	0​	75​	0​	0​	0​	#REF!​	#REF!​
SILVERWARE	0​	0​	0​	0​	0​	#REF!​	#REF!​

 

[Fluff]

How about

=SUMPRODUCT(SUMIF(INDIRECT(nrSheetParameters&"[PRODUCTS]"),tblSummary[@[PRODUCTS]:[PRODUCTS]],INDIRECT(nrSheetParameters&"["&tblSummary[[#Headers],[JAN]]&"]")))