Sumproduct across multiple sheets with multiple criteria

https://b23.tv/cACAt4l

How about this consolidate to one Sheet then sum?

select * from consolidateSheet limit 20;

cli_one_dim_c~consolidateSheet~3~8;

//select * from consolidateSheetUnion;

create temp table aa as 

select f03,属性,sum(数量) v from consolidateSheetUnion group by f03,属性 order by instr('1-Jan,1-Feb,1-Mar,1-Apr,1-May,1-Jun,1-Jul',属性);

//select * from aa;

cli_create_two_dim_no_order~aa~属性~v;

select * from aa_two_dim;

Hi,

That formula will get very complicated. It will be far easier to do this in the Query Editor. Share the download link of the file with the 11 tabs. and on the Summary tab, show the expected result in a few lines.

Hi Peiyezhu, thanks for the reply.

Here is a simplified version of what I'm trying to do:

Sheet 1

Sheet 2

Consolidated (conditionally summed) data

So:

• Each of the individual tabs have dates in the column headings, but they each start and finish at different points.
• Each tab also has a column of unique identifiers, with some crossover but it's not the same subset in each tab.
• The consolidation tab has ALL dates from all the unique tabs, and ALL unique identifiers, and I need to get a total for each unique identifiers in each of the date columns.

I managed to get this formula to work:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tab_List&"'!"&"Z"&ROW($A21)&":EP"&ROW($A21)),INDIRECT("'"&Tab_List&"'!"&"Z8:EP8"),'All Packages'!BL$8))

But this only sums a row conditional on the column date headings matching, and doesn't take into account the unique identifiers. So in the example above, consolidated tab in row 6 would total 1 for May, as it's only looking at the row number, not the unique identifiers.

The 11 tabs are necessary. This is a file that will be used ongoing for monthly a monthly process. But we also need to be able to see the combined data by month & unique identifiers in the consolidated tab.

need to sum where the date headings do not all align?

If you share your workbooks and expected result,it will be clearer to understand your question.

By the way,

have 11 tabs

How about consoldate to one sheet and then sum?