Any (clever) way to automate ROW:COL in copy/paste???

[Zilla]

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?

 

[Zilla]

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

 

[Debra Dalgleish]

In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFormat03.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]

In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I should
have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to take
care of this.

My question is do I enter your formula in all D cells?

 

[Debra Dalgleish]

My formula already has an IF function that takes care of that.

 

[Zilla]

Yes, I missed it. I did try it and it worked! Thanks again!

 

[Roger Govier]

Hi Deb

Provided the OP uses your suggestion of inserting the values in column A
and hiding some with CF, then a slightly simplified and non-volatile
solution would be to enter in D1

=IF(A2=A1,"",SUMPRODUCT(($A$1:$A$100=A1)*($C$1:$C$100="Y")*$B$1:$B$100))
and copy down

Change the length of the ranges to suit the volume of data.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.