2

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

2

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.

  • Thank you but on this formula (regexmatch(A1:A10;C1)) it is on different file. should i importrange? how can i do that? – Hakan Erdur Sep 17 at 14:16
  • =SUM(FILTER(B1:B10;arrayformula(regexmatch(A1:A10;C1)))) this worked! thanks – Hakan Erdur Sep 17 at 14:32
  • Yes, of course, you can use importrange, sorry I wasn't precise enough. – pjmg Sep 17 at 14:37
  • It is ok, thanks for the help! – Hakan Erdur Sep 17 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.