conditional formulas

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.

DJK wrote:
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

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!

DJK;344633 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96420