Q: Find cell reference containing specific value from current position up

**Greffin** · 01-29-2009, 08:42 AM

Hi.

I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.

I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.

The index column is used in some conditional formatting rules, so I would like to keep it anyway.

Example of the table:

Date         Val     Index   Sum
26.01.2009   167,1   0
26.01.2009    13,3   0
26.01.2009    58,6   0       Should contain sum of day
27.01.2009   233,2   1

It is in the Sum column I want this function. I need a function that does something like this (pseudo code):

=IF("Val" column.nextRow contains a "1") 
 THEN 
Summarize all "Val" cells from the current row and up to (not counting with) either the first blank cell or a cell containing "1")
 ELSE
Display empty string

Can this be done?

**DonkeyOte** · 01-29-2009, 08:48 AM

Perhaps a SUMIF is approrpiate, eg:

$D2: =IF($A2<>$A3,SUMIF($A$1:$A2,$A2,$B$1:$B2),"")
(note this is UK format ... so switch to ; as appropriate)

The above will not work if you have dates repeated in different blocks... eg

But I am presuming the above to not be the case.

**Greffin** · 01-30-2009, 08:04 AM

No, this is not correct. Let me elaborate the problem with an example:
This is the expected result:

      A          B             C       D      E
1    Date       Description   Price   Index  Total sum
2    26.01.2009 Some text     127     0      
3    26.01.2009 Some text      18     0      
4    26.01.2009 Some text      56     0      
5    26.01.2009 Some text      90     1      291
6    27.01.2009 Some text      63     0      
7    27.01.2009 Some text      78     1      141
8    28.01.2009 Some text      79     0      
9    28.01.2009 Some text     254     0      
10   28.01.2009 Some text      45     0      
11   28.01.2009 Some text     123     0      
12   28.01.2009 Some text      35     0      
13   28.01.2009 Some text      54     1      590

This is the algorithm it should follow:
$D: = IF($A2=$A3;0;1)
$E: = IF($D2=1;SUM($C{begin of date}:$C2);"")

The $D and $E rule will be copied down, so that in effect, $E$5=SUM($C$2:$C$5), $E$7=SUM($C$6:$C$7), $E$13=SUM($C$8:$C$13)

As you can see, there is not a fixed offset. All I know is that I should summarize $C column from x rows up from current row (row beneath a 1 in $D column) and down to the current row.

I hope this explanation was good enough.

**DonkeyOte** · 01-30-2009, 08:39 AM

Have you actually tested the solution with which you were provided? I suspect not.

Given you changed your setup such that you now have text in Column B & values in C you must change references to B to C in the original solution.

**Greffin** · 02-02-2009, 06:13 AM

Originally Posted by DonkeyOte

Have you actually tested the solution with which you were provided? I suspect not.

Actually, I did. Your solution only checks if this row's Date value is different from the next rows Date value. If it is, it summarizes the value row above with the current value row.

For $E$5, it will be =SUM($C$4:$C$5) instead of =SUM($C$2:$C$5)
For $E$7, it will be =SUM($C$6:$C$7) which is correct.
For $E$13, it will be =SUM($C$12:$C$13) instead of =SUM($C$8:$C$13)

This makes the sum wrong, and is the reason for my elaboration above. I tried to further explain the problem, because through your solution I guessed that I didn't make my problem clear enough.

**DonkeyOte** · 02-02-2009, 06:28 AM

The SUMIF provided will reference a fixed range from row 1 onwards... given the dates do not repeat out of sequence the SUMIF although referencing the range from row 1 onwards will only SUM the values in C where the date in A matches that of the current row.

The formula I provided:

D2: =IF($A2<>$A3,SUMIF($A$1:$A2,$A2,$B$1:$B2),"")

when modified to reflect your values are actually now in Col C and result in E would become:

E2: =IF($A2<>$A3,SUMIF($A$1:$A2,$A2,$C$1:$C2),"")

The above should simply be copied down for all rows for which you have data in A

I suggest you do some reading on absolute and relative referencing such that you will have a greater understanding of the formula you are presented with.

EDIT: you did not test incidentally... had you have tested you would realise your statements above do not actually hold true.