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

Issue

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

Solution

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))))

Answered By - Jose Vasquez

Answer Checked By - Candace Johnson (PHPFixing Volunteer)