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

 

[awoohaw]

Try this:
=SUMPRODUCT((AD2:AD7)*(ISNUMBER(FIND("123",AF2:AF7))))
(you may need to enter the formula with CNTR-SHFT-ENTR keystroke)

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

Cell Formulas
Range	Formula
AG1	AG1	=SUMPRODUCT((AD2:AD7)*(ISNUMBER(FIND("123",AF2:AF7))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[wonderfulsomebody123]

Try this:
=SUMPRODUCT((A5:A10)*(ISNUMBER(FIND("123",A5:A10))))
(you may need to enter the formula with CNTR-SHFT-ENTR keystroke)

Mr Excel Questions 73.xlsm
	A	B	C
1
2
3
4
5	123.00		123
6	132.00
7	231.00
8	213.00
9	312.00
10	321.00
wonderfulsomebod

Cell Formulas
Range	Formula
C5	C5	=SUMPRODUCT((A5:A10)*(ISNUMBER(FIND("123",A5:A10))))
Press CTRL+SHIFT+ENTER to enter array formulas.

=SUMPRODUCT((AF2:AF1000)*(ISNUMBER(FIND("123",AD2:AD1000))))

This code did accurately bring back the sum of 123, but only 123. Not the combinations of 123 - like 321 and so on. I don't want to enter each of the 6 combinations separately because I plan on using different combinations of numbers in the future.

 

[awoohaw]

Will it only be 3 numerals? Will they be consecutive (is "1392" a hit?)

 

[wonderfulsomebody123]

Will it only be 3 numerals? Will they be consecutive (is "1392" a hit?)

Yes they will always be 3 numerals of the combination of the number I enter. For example, if the number is 605, I want all combinations of that number including the 0 in front:

605
650
560
506
065
056

It finds those numbers and sums up the corresponding cells.

 

[awoohaw]

you didn't answer the second question.

but try this:

Mr Excel Questions 73.xlsm
	AD	AE	AF	AG
1	target:	123	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
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))) )

 

[wonderfulsomebody123]

you didn't answer the second question.

1392 should not be a hit as it's 4 digits long. Only 3 digit numbers that meet the combinations of a particular number such as 123, 321, 213, 231,132, and 312. It finds those specific combinations in column AD and sums up their cells from column AF.

Edit: that new formula didn't work for me at all.

 

[awoohaw]

ok, try the updated formula in post #6.

 

[wonderfulsomebody123]

ok, try the updated formula in post #6.

I tried, it gives me value 0, which is the wrong sum. So far the most accurate formula shows the right sum for 123. It just doesn't include all 6 combinations of 123:

=SUMPRODUCT((AF2:AF1000)*(ISNUMBER(FIND("123",AD2:AD1000))))

 

[awoohaw]

did you enter with CNTL-SHFT-ENTER keystroke?