0

I want to have a threshold value in one cell (A1) and take it as a reference for adding cells.

Suppose I have

A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6

I want to add cells based on A1(Threshold).

If A1 is 10, it would add A2:A5, sum = 13
If A1 is 9, it would add A2:A4, sum = 9

The SUM formula is

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1))

I have a formula for this first part part...

Then I want the formula to add a percentage of the value in the next cell after the threshold has been reached:

If A1 is 10 then cells from A2:A5 should be added, plus 50% of A6; sum = 16

2

The simplest way to do this would be to use an indirect on that cell the way you've already found it -

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))
<$A$1)*ROW(1:99),,))+1,1),
(0.5)*INDIRECT("A"&MAX(INDEX((SUBTOTAL(9,OFFSET($A$2,0,0,ROW(1:99),1))  
<$A$1)*ROW(1:99),,))+3))

I think the second part could be better or work off of the first part, but I haven't figured it out yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.