SUMIFS formula with horizontal and vertical criteria + REGEXMATCH

Question

I need some help trying to sum totals for different events based on horizontal and vertical criteria. I've tried doing different combinations of SUMIFS + INDEX + MATCH formulas, including transposing the data and using this one: How can I SUMIF with horizontal and vertical criteria (including dates)?. But I can't get any formulas to work.

Here's what I'm trying to accomplish:

I'm trying to sum the totals for different events based on some criteria

• channel + region
• channel + region + date

The data is found in the "data tab" and I'm trying to sum the totals in the "dashboard" tab: https://docs.google.com/spreadsheets/d/1p3cuzamRUiUAiDXQMPG4hmoHonCM8nk44diYSariBgM/edit?usp=sharing.

I need to use REGEXMATCH to match the channel name (sem or display) and the region name (uscan, emea, japac, latam).

As you can see, the campaign name & events are vertical data and the date data is horizontal. Can I get some help on the best formula(s)s to use sum the totals for the events based on these criteria?

Thanks in advance!

Answer 1

try:

=ARRAYFORMULA(QUERY({UPPER({FILTER(REGEXEXTRACT(Data!B2:B, "(?i)SEM|Display"), Data!B2:B<>""); 
 FLATTEN(IFERROR(FILTER(Data!B2:B, Data!B2:B<>"")/0)&TRANSPOSE(UNIQUE(FLATTEN(FILTER(YEAR(Data!C1:G1), ISDATE_STRICT(Data!C1:G1))))))}), 
 UPPER({FILTER(REGEXEXTRACT(Data!B2:B, "(?i)USCAN|EMEA|JAPAC|LATAM"), Data!B2:B<>""); 
 FLATTEN(TEXT(FILTER(REGEXEXTRACT(Data!B2:B, "(?i)USCAN|EMEA|JAPAC|LATAM"), Data!B2:B<>""), SEQUENCE(1, COUNTUNIQUE(FILTER(YEAR(Data!C1:G1), ISDATE_STRICT(Data!C1:G1))))))}), 
 {QUERY(QUERY(FILTER(IFERROR(1*Data!C2:G, 0), Data!B2:B<>""), "select "&TEXTJOIN("+", 1, "Col"&SEQUENCE(COLUMNS(Data!C2:G)))), "offset 1", ); 
 FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(FILTER(ROW(Data!B2:B)&"×"&YEAR(Data!C1:G1)&"×"&Data!C2:G, Data!B2:B<>"")), "×"), 
 "select sum(Col3) group by Col1 pivot Col2"), "offset 1", ))}}, 
 "select Col1,sum(Col3) group by Col1 pivot Col2 order by Col1 desc"))

demo sheet with step by step formula explanation