0

I keep a log and I wonder if there exists a formula that would sum up the values in neighbouring cells based on the value in the third column as on the image. The first "True" row must include all the previous values from the "False" rows plus the "True" value itself.

enter image description here.

For quite a long time I have to put the yellow values manually..

3 Answers 3

2

Edit: Formula simplified slightly 9E+307 => 2

This formula seems to work:

C3: =IF(AND($A3,NOT($A2)),SUM(INDEX($B:$B,1+LOOKUP(2,1/$A$2:$A2,ROW($B$2:$B2))):B3),"")

Enter in C3 and fill down.

enter image description here

algorithm

  • Return a result if and only if:
    • Cell in same row = TRUE
    • Cell in preceding row <> TRUE
  • LOOKUP(2,1/$A$2:$A2,ROW($B$2:$B2))) will return the row number of the last cell that contains TRUE in a range that ends with one row above the formula location
  • Add 1 to the start row so we skip that last TRUE cell
  • Using INDEX we return the entries in column B that correspond to the current TRUE and the preceding FALSE's
  • SUM them
3
  • Grateful that you share your great knowledge.. I am stuck at grasping 1/$A$2:$A2 What does "1/range" mean?
    – off-signer
    Commented Apr 27, 2021 at 22:43
  • 1
    @off-signer Actually that part of the formula can be simplified a bit -- see my edit. 1/range returns an array of 1 divided by each element of the range. In this case it will be an array of 1 or DIV/0 depending on the original value in the range being TRUE or FALSE Commented Apr 28, 2021 at 0:14
  • There are many different events in my log, so now I can do the required calculations between these events and place the result in a cell, neighboring that infinite column! So grateful for that release of free time!
    – off-signer
    Commented Apr 29, 2021 at 23:17
1

Well, You seem to have yourself quite the interesting problem.

This would be straightfoward if you were trying to sum from the top. But summing between the TRUEs is quite complicated.

Here is the formula I wrote to make it happen:
=IF(ROW()=1,"",IF(AND(A2,NOT(INDIRECT(CONCAT("A",MAX(ROW()-1,1))))),SUM(INDIRECT(CONCAT("B",IF(ISNUMBER(MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))),MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))+1,1))):B2),"")) Excel Output

So yah... Really gross.

This could likely be simplified with array formulas, but i'm personally not a fan of them.

Also, this is not a very portable formula, as you need to edit the column references, both in text and reference. So...

Here is an even more gross version that uses just indirect to use relative cell references:
=IF(ROW()=1,"",IF(AND(INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-2),FALSE),NOT(INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE))),SUM(INDIRECT(CONCAT("R",IF(ISNUMBER(MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))),MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))+1,1),"C",COLUMN()-1),FALSE):INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-1),FALSE)),""))

^ This formula can be dropped in any cell (without edit) and will sum the column to left of it based on the column 2-to-the-left (as shown in my image).

Explaination

Alright, buckle your seatbelts, lets break it down.

First off, the real hero in this equation is: match(2,1/([range]=TRUE))
This searches bottom-to-top in a selected range and finds the first TRUE

Now, we need the ranges for our match function to use. For this we use indirect, which takes a text string interprets it as a reference. So, for the range that each row needs to look at, we say:
=A$1:indirect(concat("A",row()-1))

If this formula is in C5, it would produce the reference range: A$1:A4

Now we can find the most recent TRUE:
=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))

Okay, we have the row number for the latest TRUE, lets sum up the range of match+1 and current row:
=sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))+1)):B1) (This B1 would change as we fill the column)

Well, thats grand! We have a functioning formula. But we only want it to output when TRUE. So lets slap an IF statement on the front to output "" when the A column is FALSE:
=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1))

Cool, now if we test out formula, we find that we have some issues... namely in row 2. This is because our match function requires an range arguments, and we provide A$1:A1 when on the first row. So, lets add another IF:
=if(row()=1,if(A1,B1,""),=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1)))

So now the formula handles row 1 on its own... But also, We can see that if we happen to have A1 set to FALSE, we get an error on row 2. This is because the match function didn't find a TRUE above it. So we need to check for "N/A" and replace the value with 1 if it is N/A (since we want to sum to the top). This requires another IF. Lets break it out of the formula to see it alone:
=if(isna(match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))),1,=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))

Great! Now slap that all together, and you have the first formula above.

Note: Some changes have been made, but the explanation hasn't been updated. Sorry

For the relative reference version, just replace all hard-references to A1,A2,B2,"B", etc. with indirect functions... And Voila!

TLDR:

Use match(2,1/[range]) and indirect to check for the most recent TRUE and then sum from there to current row. Also, handle annoying edge cases.

1

Excel 365

=IF(AND($B2=FALSE,$B3=TRUE),SUM(INDEX(C:C,MATCH(2,1/($B$1:$B2=$B3))+1):$C3),"")

Tweak (based on Ron Rosenfeld's answer)

=IF(AND(NOT($B2),$B3),SUM(INDEX(C:C,MATCH(2,1/($B$1:$B2=$B3))+1):$C3),"")

Excel 2007

=IF(AND(NOT($B2),$B3),SUM(INDEX($C$2:$C3,(LOOKUP(2,1/$B$2:$B2,$C$2:$C3))):$C3),"")

The 1+ or +1 can be eliminated from the formula if Lookup is started from previous row. The $B$2:$B2.


Breakdown:

Place formula in cell d3 downwards.

=IF(AND($B2=FALSE,$B3=TRUE),math,"")

Check for transition from FALSE to TRUE. Yes, do math, No output blank line.

math = SUM(start:$C3)

Add range of FALSE & TRUE. The end of the range is fixed by present row, but we have to do some math to find start of False data.

start = INDEX(C:C,MATCH(2,1/($B$1:$B2=$B3))+1)

Magic time. From this row, find the row of the previous TRUE. Add 1 to get to FALSE row. Generate cell reference in column C.

enter image description here


MATCH(2,1/($B$1:$B2=$B3)) - Get previous match

MATCH(2,$B$1:$B2=$B3) - Get next match

From Exceljet How to lookup first and last match

Last match with unsorted data

What if you want the last match, but data isn't sorted by lookup value? In other words, you want to apply criteria to find a match, and you simply want the last item in the data that matches your criteria? This is actually a case where the LOOKUP function shines, because LOOKUP can handle array operations natively, without control + shift + enter. This means we can dynamically build a lookup array to locate the data we want using simple logical expressions.

=LOOKUP(2,1/(item=F5),price)
1
  • I tamed the Lookup function to work with arrays, but the Match function does not work that way on my side! Let me ask you what Excel version you have? In order to get the last Match, this function must be entered with control + shift + enter.
    – off-signer
    Commented Apr 29, 2021 at 8:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .