Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
=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! |
#4
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |