Sumif combinations of a number

[wonderfulsomebody123]

=SUMIFS(AD2:AD1000, AF2:AF1000, "*123*")

The formula above is supposed to find all combinations of 123 in column AD and sums up the corresponding cells in column AF. By combinations of 123, I mean:

123
132
231
213
312
321

The code just returns the value "0" to me. What can be done? Take note, I use Excel 2007.

Thank you

 

[wonderfulsomebody123]

did you enter with CNTL-SHFT-ENTER keystroke?

Yes

 

[wonderfulsomebody123]

did you enter with CNTL-SHFT-ENTER keystroke?

=SUMPRODUCT((AD2:AD7)*(ISNUMBER(FIND(MID(AE1,1,1),AF2:AF7)))*(ISNUMBER(FIND(MID(AE1,2,1),AF2:AF7)))*(ISNUMBER(FIND(MID(AE1,3,1),AF2:AF7))))

Odd thing, when I enter this formula in a new sheet with the numbers you provided, it works. But on my sheet with my numbers, the same formula always gives me 0. Could it be misreading the numbers some how? I even tried a helper column for the same numbers but it just won't work properly.

 

[awoohaw]

then your numbers are probably text or in cells that are formatted as text. To check this, change the format of the cells to General or try to add decimal spaces.

 

[awoohaw]

=SUMPRODUCT((AD2:AD7)*(ISNUMBER(FIND(MID(AE1,1,1),AF2:AF7)))*(ISNUMBER(FIND(MID(AE1,2,1),AF2:AF7)))*(ISNUMBER(FIND(MID(AE1,3,1),AF2:AF7))))

Odd thing, when I enter this formula in a new sheet with the numbers you provided, it works. But on my sheet with my numbers, the same formula always gives me 0. Could it be misreading the numbers some how? I even tried a helper column for the same numbers but it just won't work properly.

I'm happy you marked one of my posts as the answer. But, are you sure the post you selected was the best answer given?

 

[wonderfulsomebody123]

I'm happy you marked one of my posts as the answer. But, are you sure the post you selected was the best answer given?

=SUMPRODUCT((C2:C1000)*
(ISNUMBER(FIND(MID(D2,1,1),A2:A1000)))*
(ISNUMBER(FIND(MID(D2,2,1),A2:A1000)))*
(ISNUMBER(FIND(MID(D2,3,1),A2:A1000)))
)

Your formula works perfect when all 3 digits are different. But when 2 or 3 digits are same, the formula fails. I've been trying to figure it out on my own - didn't want to bother you as you've already helped a lot. I've been trying to get that to work if the number in D2 is 000 or 333, 002, 100 - numbers like that fail the formula.

 

[awoohaw]

Okay, how are you typing in three zeros and having it display? Are you using text?
If you format your search criteria cell as 000.# (you'll always have the decimal) it works for me:

Mr Excel Questions 73.xlsm
	AD	AE	AF	AG
1	target:	333.	Sum:	731
2	120		123.00
3	121		132.00
4	121		231.00
5	123		213.00
6	124		312.00
7	122		321.00
8
wonderfulsomebod

Cell Formulas
Range	Formula
AG1	AG1	=SUMPRODUCT((AD2:AD7)* (ISNUMBER(FIND(MID(AE1,1,1),AF2:AF7)))* (ISNUMBER(FIND(MID(AE1,2,1),AF2:AF7)))* (ISNUMBER(FIND(MID(AE1,3,1),AF2:AF7))) )