sum with criteria
 

I have an invoice & payments tracker for sub-contractors, im trying to create
a formula which will allow me to calculate how much each contractor is owed.

For each job, the subbie is allocated a proportion of the total invoice, and
gets paid when the client pays

At the moment I have something like this:

Subbie Subbies commission Date Client Paid
Adam 100
John 200 1/09/2005
John 50
Adam 300 5/09/2005
John 100

I need a formula to add all of Adams commissions which have NOT been paid
by the client, ie = 100

Then another formula to add all of Johns commissions which have NOT been
paid by the client, ie = 150

Im sure its really simple, Ive been trying to figure it out but keep
getting #VALUE errors

Thanks heaps!

Hi!

Try this:

=SUMPRODUCT(--(A1:A10="Adam"),--(C1:C10=""),B1:B10)

Better to use a cell to hold the name:

D1 = Adam

=SUMPRODUCT(--(A1:A10=D1),--(C1:C10=""),B1:B10)

Biff

"meris" wrote in message
...
I have an invoice & payments tracker for sub-contractors, im trying to
create
a formula which will allow me to calculate how much each contractor is
owed.

For each job, the subbie is allocated a proportion of the total invoice,
and
gets paid when the client pays

At the moment I have something like this:

Subbie Subbie's commission Date Client Paid
Adam 100
John 200 1/09/2005
John 50
Adam 300 5/09/2005
John 100

I need a formula to add all of Adam's commissions which have NOT been paid
by the client, ie = 100

Then another formula to add all of John's commissions which have NOT been
paid by the client, ie = 150

I'm sure its really simple, I've been trying to figure it out but keep
getting #VALUE errors

Thanks heaps!

=SUMPRODUCT(--(A2:A10="Adam"),--(C2:C10<""),B2:B10)

replace "Adam" with a cell reference and then change the name in the cell,
this assumes that empty cells (in date range) are not paid and non empty
paid

--
Regards,

Peo Sjoblom

(No private emails please)


"meris" wrote in message
...
I have an invoice & payments tracker for sub-contractors, im trying to
create
a formula which will allow me to calculate how much each contractor is
owed.

For each job, the subbie is allocated a proportion of the total invoice,
and
gets paid when the client pays

At the moment I have something like this:

Subbie Subbies commission Date Client Paid
Adam 100
John 200 1/09/2005
John 50
Adam 300 5/09/2005
John 100

I need a formula to add all of Adams commissions which have NOT been paid
by the client, ie = 100

Then another formula to add all of Johns commissions which have NOT been
paid by the client, ie = 150

Im sure its really simple, Ive been trying to figure it out but keep
getting #VALUE errors

Thanks heaps!

Oops! Change the <"" to ="" like in Biff's answer, I reversed it

--
Regards,

Peo Sjoblom

(No private emails please)


"Peo Sjoblom" wrote in message
...
=SUMPRODUCT(--(A2:A10="Adam"),--(C2:C10<""),B2:B10)

replace "Adam" with a cell reference and then change the name in the cell,
this assumes that empty cells (in date range) are not paid and non empty
paid

--
Regards,

Peo Sjoblom

(No private emails please)


"meris" wrote in message
...
I have an invoice & payments tracker for sub-contractors, im trying to
create
a formula which will allow me to calculate how much each contractor is
owed.

For each job, the subbie is allocated a proportion of the total invoice,
and
gets paid when the client pays

At the moment I have something like this:

Subbie Subbies commission Date Client Paid
Adam 100
John 200 1/09/2005
John 50
Adam 300 5/09/2005
John 100

I need a formula to add all of Adams commissions which have NOT been
paid
by the client, ie = 100

Then another formula to add all of Johns commissions which have NOT been
paid by the client, ie = 150

Im sure its really simple, Ive been trying to figure it out but keep
getting #VALUE errors

Thanks heaps!