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
(Remove XSPAM)

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

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

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" 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

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

Zilla wrote:
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" 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






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

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

"Debra Dalgleish" wrote in message
...
My formula already has an IF function that takes care of that.

Zilla wrote:
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" 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






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

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.
--
Regards

Roger Govier


"Debra Dalgleish" wrote in message
...
My formula already has an IF function that takes care of that.

Zilla wrote:
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" 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






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

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

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

"Debra Dalgleish" wrote in message
...

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

Zilla wrote:

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" 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





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






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