Search code examples
google-sheetsgoogle-sheets-formulavlookuparray-formulasgoogle-query-language

Index-Match with Sum-Function

I have two tabs Product-Overview and Prices.

On the Prices-tab I would like to add my data and get an overview on the financials tab.

enter image description here

I was trying to use index-match, however if there are several values in the same month in my Prices-tab I am not sure how to sum the up?

I would like to get the following result:

enter image description here

Find below my minimum viable example as google sheet:

Google Sheet

Any suggestions how to do this?

Solution

  • delete column D and use this in D2:

    =ARRAYFORMULA(IF(A2:A="";;TEXT(B2:B; "mm/yy")))

    enter image description here

    then you can do this:

    =QUERY(Prices!A2:D; "select A,sum(C) where A is not null group by A pivot D")

    and more advanced in O5:

    =ARRAYFORMULA(IF(O1:1="";;IF(B5:B="";;
 IFNA(VLOOKUP(B5:B; QUERY(Prices!A2:D; 
 "select A,sum(C) where A is not null group by A pivot D"); 
 MATCH(TO_TEXT(O1:T1); QUERY(QUERY(Prices!A2:D; 
 "select sum(C) where A is not null group by A pivot D"); 
 "limit 0"; 1); 0)+1; 0))*1)))

    enter image description here

    of course B5 can be:

    =SORT(UNIQUE(Prices!A2:A))

    enter image description here

    then D5 can be:

    =ARRAYFORMULA(IF(B5:B="";;"Product "&REGEXEXTRACT(TO_TEXT(B5:B); ".(\d+)")))

    enter image description here

    average N5 can be done like:

    =ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(O5:100);
 "select "&TEXTJOIN(","; 1; IF(B5:B="";; 
 "avg(Col"&ROW(O5:O)-ROW(O5)+1&")"))&""));
 "select Col2"))

    enter image description here

    generating dates in O1 like:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(TRANSPOSE(UNIQUE(TEXT(ROW(INDIRECT(
 DATEVALUE("1/11/2020")&":"&
 DATEVALUE("1/11/2050"))); "mm/yy"))); 1; COLUMNS(O1:1)-1))

    enter image description here

    and years in O2 like:

    =ARRAYFORMULA(IFNA(20&REGEXEXTRACT(O1:1; "/(\d+)")))

    0