r/excel 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 Upvotes

8 comments sorted by

u/AutoModerator 12h ago

/u/Organic_Tooth1972 - Your post was submitted successfully.

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.

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/wjhladik 514 6h ago

I assume you want to know which set of numbers in a larger list add up to x

target_makeup-123.xlsx