Multi text string single cells how do sumifs

[silambarasan.J]

Hi Excel Forum.

Sheet:1:Sample Data:

A B C D

Code Date Amount Type
3100 29/05/2015 1,570.44 351
3100 29/05/2015 1,945.42 351
3100 29/05/2015 758.57 351
3100 29/05/2015 215.4 351
3100 29/05/2015 787.93 351
3100 29/05/2015 83.22 351
3100 29/05/2015 63,096.91 351
3190 04/05/2015 59.33 351
3190 04/05/2015 268.52 351
3190 04/05/2015 53.05 351
3190 04/05/2015 153.79 351
3190 04/05/2015 2,066.32 351
3190 05/05/2015 2,066.32 351
3190 19/05/2015 41.4 351
3190 28/05/2015 347.89 351
3700 23/05/2015 5,466.82 351
3700 28/05/2015 1,613.87 351
3700 28/05/2015 4,302.05 351
4200 02/05/2015 23,904.52 351
4200 02/05/2015 11,305.39 351
4200 02/05/2015 5,247.90 351
4200 02/05/2015 628.03 351
4200 02/05/2015 684.23 351
4200 02/05/2015 4,469.13 351
6500 04/05/2015 145.74 351
6500 04/05/2015 22,896.22 351
6500 04/05/2015 1,323.60 351
6590 04/05/2015 54.06 351
6590 04/05/2015 1,626.81 351
6590 04/05/2015 934.47 351

Sheet:2:Required Format:

A B

Code: May-15

3100-3150-3190-3700 84,897.25

4200 46,239.20

6500-6590 26980.9

D3=SUMIFS(Data!$C:$C,Data!$A:$A,'Req Format'!$A3,Data!$B:$B,">="&EOMONTH('Req Format'!$D$2,-1)+1,Data!$B:$B,"<="&EOMONTH('Req Format'!$D$2,0))

Am trying to the following formula sumifs corresponding to the criteria range and month against return the value is perfect when sumifs criteria range one

text string (4200) the same was am apply to another cells return the value is -.when sumifs criteria range multi text string in single cells(3100-3150-3190-

3700)

my query is How do sumifs ,multi text string in single cell if possible.please give me the solution.

file attached.

[samba_ravi]

B3=SUMPRODUCT(ISNUMBER(SEARCH(Data!$A$2:$A$45875,$A3))*(Data!$C$2:$C$45875)*(TEXT(Data!$B$2:$B$45875,"MMYY")=TEXT(B$2,"MMYY")))

Please Login or Register  to view this content.

OR
B3=SUMPRODUCT(SUMIFS(Data!$C:$C,Data!$A:$A,INDEX(TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",LEN(A3))),(ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,"-",""))+1))-1)*LEN(A3)+1,LEN(A3))),0),Data!$B:$B,">="&EOMONTH('Req Format'!$D$2,-1)+1,Data!$B:$B,"<="&EOMONTH('Req Format'!$D$2,0)))

Please Login or Register  to view this content.

Try this and copy towards down

[silambarasan.J]

Siva Sir you are amazing and Excellent.both of formulas working is perfect and main thick your second formula was miracle.