Forum Discussion
Filtering Records Based On A Criteria Value
My requirement is to filter records based on a criteria value.
List of records in Column A (Filter range)
Criteria in Column B
Output in Column C
Criteria is sum of three different ranges in Column A
A VBA code is required provide output in Column C that these values are equal or sum of criteria.
| Filter Range | Criteria | Output |
| 1394 | 3232.83 | |
| 1386.64 | ||
| 532 | ||
| 26 | ||
| 486 | ||
| 220.66 | ||
| 1062 | ||
| 400 | ||
| 126.27 | ||
| 170 | ||
| 3810 | ||
| 700 | ||
| 500 | ||
| 22 | ||
| 120 | ||
| 230 | ||
| 35.65 | ||
| 421.74 | ||
| 2129.56 | ||
| 241.5 | ||
| 234.78 |
I think you need to add some clarity to your inquiry.
When you say "Criteria is sum of three different ranges in Column A" do you mean you want to find
- any three cells in Column A where the sum equals the stated criterion, or
- any three cells in Column A where the sum is less than or equal to the stated criterion, or
- any three contiguous cells in Column A where the sum equals the stated criterion, or
- any three contiguous cells in Column A where the sum is less than or equal to the stated criterion
- or every combination of cells, be they contiguous or not...
- etc
What is the result supposed to look like? Identification of the cells in question? Just a series of totals meeting the criterion? Does the result have to be in column C?
In short, your question raises more questions that need to be answered. Once you've answered them, you might have solved your own problem.
any three contiguous cells ranges in Column A where the sum equals the stated criterion
Just the series of values meeting the criterion.
Result to be in Column C
A more illustrated table is pasted here.
Background of the problem:
We have received payment form a customer who has many bills outstanding, may be hundreds of bills are outstanding. Upon receipt of payment, we need to identify bills those make sum of this payment which we received.
Filter Range Criteria Output Cell
Reference
Cell Total 1394 3232.83 1386.64 532 26 486 A6:A9 2168.66 220.66 A15:A19 829.39 1062 A22 234.78 400 3232.83 126.27 170 3810 700 500 22 120 230 35.65 421.74 2129.56 241.5 234.78 You seem to have agreed with the three contiguous cells as part of the criterion, so here's a sample of how that can be done, with a cell off to the right that gives a margin of error, since it's not always going to be the case that you get exactly that criterion amount.
But this only looks at the sum of each set of three contiguous cells. Those combinations that come within the percentage margin of error are highlighted in green.
