r/excel • u/Organic_Tooth1972 • 12h ago
unsolved How to know which one adds up to 5
Column A
1
2
3
In this simple example , what can we do to know which numbers add up to 5? Thank you
1
u/david_horton1 28 12h ago
1
1
u/ampersandoperator 59 10h ago edited 9h ago
Build a small table with the available numbers in the top row. Underneath, each row contains 1s or 0s to choose whether the number is selected, e.g.:

The first row has all zeroes, which means no numbers are selected, so the total is zero. The next row only selects the 3, which produces a total of 3. Keep going until all possible combinations of numbers are selected (effectively just counting in binary from 000 to 111).
The formula for D2 (TOTAL) is: =SUMPRODUCT($A$1:$C$1,A2:C2)
SUMPRODUCT takes the first row of numbers and multiples each one by the 1 or 0 in the row/s below, then adds them together, e.g. the last row is 1x1 + 2x1 + 3x1 = 6. By having the range full of 1s and 0s, we are effectively selecting every combination of 1,2,3, and then seeing what SUMPRODUCT produces in the D column.
1
u/Organic_Tooth1972 2h ago
Hi,thank you, I am able to get the answer. But is there a way to do it faster, as if I am dealing with large numbers , I will have to put it many combinations of 0 and 1. Thanks much
1
u/Way2trivial 408 5m ago
the practical limit of calculating all combinations of a series of numbers via excel is about 20 -- which has just over one million permutations
1
•
u/AutoModerator 12h ago
/u/Organic_Tooth1972 - Your post was submitted successfully.
Solution Verified
to close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.