If date range, then % commission.Closed

Question

I am trying to create a formula that will look at a group of rows and calculate a percentage of a particular date range of money.  The rows have a date and, sometimes, a dollar amount.

Column A = Date
Column B = Cash received
Column C = Balance

20 Rows of data with dates ranging from Jan 1 2014 to March 31 2014.  I am looking for one cell to represent 5% of total cash received during Jan 1 - Feb 26.  Then I am looking for another cell to represent 3% of total cash received between Feb 27 - March 15.

I hope this makes sense.

ac3mark · Answer

Ok, so do this, it you need further explaination, let us know:

=SUM(A1:A18)*0.05
=SUM(A19:A30)*0.03


It really is that simple!

If you need help delimiting the date, then you need to become familiar with countif.

Or you could always sort by date?

Have FUN!
 
"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon

ace350 · Answer

This method will not work in this case.  The spreadsheet will be used 80 times with different data every time.  There needs to be a way to search a range, identify rows with a date in range, add the cash received and multiply by the %.

ac3mark · Answer

Oh, so you are looking for a form, to input the date to look for?

Like a search form?

ace350 · Answer

I was not aware of the term "Search Form", but that seems to describe what I am looking for.

ac3mark · Answer

Ok, you say that there needs to be a search, based on what, the day?

If your formulas reference the current day would that matter?   I am not understanding, how you wish to have this "task" started?

In your formula, qualify the amount with an if statement:

Like this:
=IF(A1<2/28/2013,B1*0.05,B1*0.03)



"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon

If date range, then % commission.

5 responses

Similar discussions

Subscribe To Our Newsletter!