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