conditional formulas

[DJK]

I am trying to track data by using a reference in a separate column. For
example, If cell A1="Victor" I want to deduct the data assigned to him in
cell B1 on the condition that cell C1="Y"

So, If "Victor" is assigned to A1:A10, and B1:B10 is assigend 5 each
totaling 50, as Victor completes the assignment for each row indicated by a
"Y" in the next cell, I want cell D4 to reflect the open tasks while D5 will
reflect the completed.

 

[smartin]

I am trying to track data by using a reference in a separate column. For
example, If cell A1="Victor" I want to deduct the data assigned to him in
cell B1 on the condition that cell C1="Y"

So, If "Victor" is assigned to A1:A10, and B1:B10 is assigend 5 each
totaling 50, as Victor completes the assignment for each row indicated by a
"Y" in the next cell, I want cell D4 to reflect the open tasks while D5 will
reflect the completed.

D4: =SUMPRODUCT(--("Victor"=A1:A10),--("Y"<>C1:C10),(B1:B10))
D5: =SUMIF(A:A,"Victor",C:C)-D4

 

[Simon Lloyd]

This will show all those open (without a Y in column C)
=SUMPRODUCT((A1:A10="Victor")*(B1:B10=5)*(C1:C10<>"Y")) and this will
show all those complete
=SUMPRODUCT((A1:A10="Victor")*(B1:B10=5)*(C1:C10="Y")) bear in mind that
the ranges must be the same!

I am trying to track data by using a reference in a separate column. For
example, If cell A1="Victor" I want to deduct the data assigned to him
in
cell B1 on the condition that cell C1="Y"

So, If "Victor" is assigned to A1:A10, and B1:B10 is assigend 5 each
totaling 50, as Victor completes the assignment for each row indicated
by a
"Y" in the next cell, I want cell D4 to reflect the open tasks while D5
will
reflect the completed.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)