# Find Expected SUM

#### A Durfani

##### Board Regular
Is it possible to find expected SUM from one sheet to another?

Below Sheet Blalock2 amounts in column B have may have SUM from Blalock1

For Example Amount in cell B3 is 5,498.35 so I want to find smaller amounts from Blalock1 which totals 5,498.35

Blalock2.xlsx
AB
1DATE 2SPENT 2
21/1/2021408.41
31/1/20215,498.35
41/1/20217,441.35
51/1/20218,252.56
61/1/202111,387.92
71/1/202114,378.45
81/15/20217,188.82
91/15/20211,156.88
101/15/20212,279.66
111/15/20218,329.83
121/15/20214,860.35
131/15/20219,677.81
141/15/20217,084.41
151/15/202110,196.50
161/22/20214,511.33
171/22/20216,298.02
181/22/202114,418.90
191/22/202118,232.86
201/23/202151.29
211/30/20215,957.85
221/30/20217,900.31
231/30/202119,177.32
241/30/20218,337.11
251/30/20218,535.07
261/30/20217,228.74
271/30/202112,137.10
281/30/202112,669.43
291/30/20215,792.62
Blalock2

Sheet Blalock1

Blalock1.xlsx
AB
1Date 1Spent 1
201/13/202121.44
301/13/202172.01
401/13/202176.11
501/13/202176.11
601/13/202181.36
701/20/2021149.50
801/13/2021151.23
901/20/2021187.30
1001/20/2021216.47
1101/13/2021232.77
1201/13/2021233.18
1301/20/2021243.55
1401/13/2021294.05
1501/13/2021305.03
1601/20/2021348.72
1701/20/2021413.04
1801/20/2021517.28
1901/13/2021518.55
2001/20/2021552.15
2101/20/2021627.72
2201/20/2021664.43
2301/20/2021789.07
2401/13/20211,104.97
2501/13/20211,197.34
2601/20/20211,711.07
2701/13/20213,369.88
2801/13/20214,306.61
2912/29/20204,356.95
3001/20/20214,615.76
3101/13/20214,791.87
3201/13/20214,838.91
3312/30/20205,677.67
3401/20/20215,692.37
3501/13/20215,897.00
3612/30/20206,406.86
3701/13/20216,413.22
3801/13/20216,851.58
3901/13/20216,883.79
4012/30/20207,129.34
4101/13/20218,353.83
4201/13/20219,010.17
4301/20/20219,222.04
4401/13/20219,605.80
4512/31/202010,970.97
4601/20/202114,526.44
Sheet1

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### GlennUK

##### Well-known Member
Do you have any idea how difficult that is? Your example (to use as an example) of 5,498.35 ... can be any combination of either 2, or 3, or 4 ... or "n" from 31 numbers on the other list (only taking the ones less than the total). So that is more that 2 trillion possibilities. It goes up from that for when it's more than 31 numbers (i.e. bigger totals).

#### A Durfani

##### Board Regular
Do you have any idea how difficult that is? Your example (to use as an example) of 5,498.35 ... can be any combination of either 2, or 3, or 4 ... or "n" from 31 numbers on the other list (only taking the ones less than the total). So that is more that 2 trillion possibilities. It goes up from that for when it's more than 31 numbers (i.e. bigger totals).
Yes I know how difficult is it I need to do it manually that is why I am searching for any easy way for doing it.

#### GlennUK

##### Well-known Member
Look at this explanation of how Solver might help. I tried on your 5,498.35 example, and had to kill it after it had been running for 30 minutes (it got near in that time, but not a complete solution).

#### A Durfani

##### Board Regular

Look at this explanation of how Solver might help. I tried on your 5,498.35 example, and had to kill it after it had been running for 30 minutes (it got near in that time, but not a complete solution).
Thank you for your patience and help
will try with solver

#### Dave Patton

##### Well-known Member
Search this forum for Accounts Receivable Problem.
Try one or more of the solutions suggestions.
Post #8 would probably work for your question.

#### A Durfani

##### Board Regular
Search this forum for Accounts Receivable Problem.
Try one or more of the solutions suggestions.
Post #8 would probably work for your question.

Thank you very much Dave Patton this works perfect and very fast

Writer of this code is Legend...

Replies
1
Views
117
Replies
7
Views
125
Replies
4
Views
81
Replies
8
Views
171
Replies
2
Views
208

1,127,457
Messages
5,624,853
Members
416,063
Latest member
chaulon199

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back