Google-sheets – How to sum the values of same vlookup results

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I have this formula it gets the single data from another file from a sheet at column 7.

    =ARRAYFORMULA(
        if(
          len($A2:$A);
          iferror(
              vlookup(
                 $A2:$A;
                 importrange('Aylar ve Linkler'!$B$7;"Grafik Data'!$C3$:$J$108");
                 7;0);
                 "mağaza yok")
          ;)
      )

But the stores vlookup($A2:$A) on the other file's Grafik Data'!$C3$:$J$108sheet at 7th columns has 2 values and i want to sum them.

I mean when i look for store names on other file, there are 2 same store names with 2 different values. I would like to get sum of them.

sample data

Best Answer

There are multiple ways to do this : Query, Filter but I think SUMIF will suits the most : 

=SUMIF(arrayformula(regexmatch(A1:A10;C1));true;B1:B10)

=SUM(FILTER(B1:B10;arrayformula(regexmatch(A1:A10;C1))))

=trim(REGEXREPLACE(CONCATENATE(QUERY(A1:B10;"SELECT sum(B) where A = '"&C1&"'"));"sum";))

Spreadsheet : 

   A    |    B   |   C
--------------------------
Store 1 |    0   | Store 1
Store 1 |    12  |
Store 1 |    5   |
Store 2 |    0   |
Store 2 |    3   |
Store 2 |    4   |
Store 3 |    0   |
Store 3 |    8   |
Store 3 |    20  |

Result : 17.
Because I used Regexmatch, you can't use it in a single arrayformula, you'll need to expand the formulas.

Related Topic