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

Z

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?
 
Z

Zilla

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

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?
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top