Thanks, I thought about the duplication, but NOT about
the hiding using conditional formatting.

"Debra Dalgleish" wrote in message
...
In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:
Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html