Forum Discussion
Filtering Records Based On A Criteria Value
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.
- amjadinsaudiaOct 11, 2023Copper Contributor
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 - mathetesOct 11, 2023Silver Contributor
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.
- amjadinsaudiaOct 12, 2023Copper ContributorNo, I am not agreed that always three contiguous cells will be part of criteria.
Any combination of cells either contiguous or separate or starting cells or ending cells, could make a total which will be equal to criteria.
Any further assistance will be much appreciated.
This workbook is not fulfilling the requirement and there will be a need of a VBA code.
Thanks.
- peiyezhuOct 17, 2023Bronze Contributor3232.83:
22,35.65,120,220.66,230,234.78,400,421.74,486,1062
26,35.65,170,220.66,230,234.78,421.74,500,1394
26,35.65,220.66,234.78,400,421.74,500,1394
26,35.65,220.66,234.78,421.74,532,700,1062
sum ?