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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Can this be done?

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

Perhaps a SUMIF is approrpiate, eg:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

**Greffin** · 02-02-2009, 02:39 PM

I wish to retract my previous statement about this formula not working. I had mistyped the formula, and ended up with a '$' character or two short.

The formula you provided works remarkably. Thank you very much for this, and your patience in convincing me that you were right.

**VBA Noob** · 02-02-2009, 02:40 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save