I have the need to sum a column based on names and dates. I have a spreadsheet that contains the following named columns(type of data in parenthesis):
SalesOwner(text, i.e.: employee name), FirstInvoiceDate(date field), DocDate(date field), Billings(numeric). What I need to do is this: I need to sum the Billings column IF the SalesOwner column = Given employee name AND the Docdate is less than or equal to FirstInvoiceDate + 365.
(this is a sales bonus calculation where the sales owner gets a monthly bonus for the first year that we bill the client. Docdate refers to the current invoice, FirstInvoiceDate refers to the first time we invoiced the client.
I was trying to use DateValue to get the value of the FirstInvoiceDate and DocDate but it doesn't seem that you can use that function with a range, which is what these columns are.
My attempts look something like this:
SUM((DATEVALUE(DocDate)<=DATEVALUE(FInvDate)+365)*(SalesOwner="Bonnie")*Billings)
Any ideas that ANYONE has would be greatly appreciated!!
SalesOwner(text, i.e.: employee name), FirstInvoiceDate(date field), DocDate(date field), Billings(numeric). What I need to do is this: I need to sum the Billings column IF the SalesOwner column = Given employee name AND the Docdate is less than or equal to FirstInvoiceDate + 365.
(this is a sales bonus calculation where the sales owner gets a monthly bonus for the first year that we bill the client. Docdate refers to the current invoice, FirstInvoiceDate refers to the first time we invoiced the client.
I was trying to use DateValue to get the value of the FirstInvoiceDate and DocDate but it doesn't seem that you can use that function with a range, which is what these columns are.
My attempts look something like this:
SUM((DATEVALUE(DocDate)<=DATEVALUE(FInvDate)+365)*(SalesOwner="Bonnie")*Billings)
Any ideas that ANYONE has would be greatly appreciated!!
