Search code examples
excelexcel-formula

Calculate SUM if criteria appears in a string in the criteria_range

A B C D E F
1 1,4,7,10 500 1 560 (=500+60)
2 2,5,8,11 300 2 300 (=300)
3 3,6,9,12 800 3 800 (=800)
4 1,9 60 4 500 (=500)
5 5,12 90 5 390 (=300+90)
6 6,7,12 30 6 830 (=800+30)
7 7 530 (=500+30)
8 8 300 (=300)
9 9 860 (=800+60)
10 10 500 (=500)
11 11 300 (=300)
12 12 920 (=800+90+30)
13

As you can see in Column A the values in the cells are a string.
In Column D each number that appears in Column A is listed separately.
The idea is now to calculate the SUM in Column E whenever one of the numbers in Column D appears in a string in Column A.

Something like this: 

=SUMIFS($B$1:$B$6,$A$1:$A$6,If number in cell D1 appears in one of the strings in Column A)

(Note: I only added Column F for a better explanation how the numbers in Column E are calculated)

Do you have any idea how to achieve this?

Solution

  • Try using the following formula:

    enter image description here

    =MAP(D1:D12,LAMBDA(α,SUM(FILTER(B1:B6,1-ISERR(FIND(","&α&",",","&A1:A6&","))))))

    Or, To copy down can use:

    =SUM(FILTER(B$1:B$6,1-ISERR(FIND(","&D1&",",","&A$1:A$6&","))))

    Or,

    =SUM(IF(1-ISERR(FIND(","&D1&",",","&A$1:A$6&",")),B$1:B$6,0))