# If date range, then % commission.

Closed
ace350 Posts 3 Registration date Monday April 7, 2014 Status Member Last seen April 8, 2014 - Apr 7, 2014 at 06:32 PM
Blocked Profile - Apr 8, 2014 at 05:06 PM
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 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.

## 5 replies

Blocked Profile
Apr 7, 2014 at 07:33 PM
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 Posts 3 Registration date Monday April 7, 2014 Status Member Last seen April 8, 2014
Apr 7, 2014 at 11:52 PM
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 %.
Blocked Profile
Apr 8, 2014 at 08:36 AM
Oh, so you are looking for a form, to input the date to look for?

Like a search form?

ace350 Posts 3 Registration date Monday April 7, 2014 Status Member Last seen April 8, 2014
Apr 8, 2014 at 12:49 PM
I was not aware of the term "Search Form", but that seems to describe what I am looking for.

Didn't find the answer you are looking for?

Blocked Profile
Apr 8, 2014 at 05:06 PM
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