CODEWITHSUNDEEP
google-sheetsspreadsheetsumifsmultiple-conditions
AdOne
AdOne

Reputation: 131

How to apply multiple conditions from one cell for SUMIF | GoogleSheets

Do you have an idea for a function that would sum the amounts from table 2 based on Unique_nr from Table 1?

I tried to do it this way:

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{SPLIT(A3,",")},F3:F9))) <---doesn't work

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{"8-1","9-1"},F3:F9))) <----It works

Theoretically the SPLIT() function gives the same result as I type manually, but unfortunately it doesn't work.

I would like to do this with one function for the entire range of data

https://docs.google.com/spreadsheets/d/1JGvFIZIE6c_D0A2Z4xCWf7pxqVft4Zsb6S-45_d9LY4/edit?usp=sharing enter image description here

Upvotes: 0

Views: 284

Answers (2)

Jose Vasquez
Jose Vasquez

Reputation: 1728

You were almost in the correct way

First of all, remove the double quotes from the cells.

By default SPLIT will Divide text around a specified character or string and it means there will be an extra step in order to use this output to another function, it's possible that your cell had an extra character and the TRIM function will solve it.

=SUM(ARRAYFORMULA(SUMIF(E3:E9,{trim(SPLIT(A3,","))},F3:F9)))

You can use VLOOKUP and SUM as a different approach

As you mentioned SPLIT is a good approach to treat comma-separated cells. In order to avoid unexpected spaces TRIM is a good option (it's optional) as well as IFNA in order to fill that cell in case there's not a match.

=ArrayFormula(SUM(IFNA(vlookup(trim(split(A3,",")),E3:F9,2,0))))

Upvotes: 1

basic
basic

Reputation: 11968

If you can't find a better option, you can use a dragable formula:

=ArrayFormula(SUM($F$3:F*(ISNUMBER(SEARCH($E$3:E,A3)))))

enter image description here

Upvotes: 1

Related Questions