CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
vitto
vitto

Reputation: 19496

Google Spreadsheet - Sum cell value if another cell contain one of N strings

As shown on this Google Spreadsheet I would like to SUM a list of currency values from a column only if in the row of the value I have one of the selected strings from a list.

| Tag    | Value |
| :      |     : |
| Goo    |   12$ | <= SUM value because I have Goo or Boo
| Dee    |    3$ |
| Boo    |    4$ | <= SUM value because I have Goo or Boo
| Yoo    |    7$ |
| :      |     : |
| Result |   16$ |

I didn't found a way to do that, is that possible?

Upvotes: 2

Views: 2024

Answers (2)

Michal
Michal

Reputation: 6131

enter image description here

=ArrayFormula(SUMPRODUCT((A2:A5=({"Goo","Boo"}))*(B2:B5))) - SUMIF has a lot of inherent limitations, you would do well to read something about array formulas.

Upvotes: 2

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You can just sum two SUMIF() functions together.

=SUMIF(A:A,"Goo",B:B) + SUMIF(A:A,"Boo",B:B)

Assuming that A:A contains the words to match and B:B contains the values to sum.

To be more specific to your issue, you will actually have to modify your range so you are not getting any circular reference errors (since the cell is in the same column as your sum range)

So, if the cell that contains your formula is in row 25 (for example), then something like this should work:

=SUMIF(A1:A24,"Goo",B1:B24) + SUMIF(A1:A24,"Boo",B1:B24)

Upvotes: 3

Related Questions