# Calculating 2007 Commissions vs 2006?

#### DukeVBall

##### New Member
Hey everyone,

I'm in retail real estate and I keep track of my commissions on excel. In leasing we get paid at lease signing, then when the tenant opens. The first check could be in 2006 and the second 2007. However, I'm trying to track my 2007 earnings without having to delete or zero out my 2006. Otherwise, when I sum the total, I get 2006 and 2007 numbers.

This is what I was trying to do:

If the date commission is paid falls between 1/1/2007 and 12/31/2007, then add it. If it does not, disregard it or make it 0.

I want to have a sum that pulls the amount earned from 2007...if anyone could think of a way to do this, please let me know. I can send a sample of my worksheet.

dukevball @ gmail.com

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Scott Huish

##### MrExcel MVP
Hi, welcome to the board!

Assuming dates in Column A and amounts in Column B:

Try this:
=SUMPRODUCT(--(YEAR(A1:A1000)=2007),B1:B1000)

or if you always want to show for the current year, i.e. in 2008 you won't have to change the formula for the year:

=SUMPRODUCT(--(YEAR(A1:A1000)=YEAR(TODAY())),B1:B1000)

#### debooo

##### Board Regular
why dont you just put a header on both col's date and comission.

then you can do a custom sort and just show any date range you want

#### Scott Huish

##### MrExcel MVP
debooo:
How would that sum anything?

#### debooo

##### Board Regular
that in itself would not sum anything. i just think it gives you more flexibility if you are just examining your earnings.

after you sorted it you could just have a sum function at the bottom to find out how much you have made.

also, it would not limit you to only looking at 1 yr vs another, you could look at previous 3 months, previous 6month, etc, and sum any of those that you wanted.

##### MrExcel MVP
or use a pivot table

Replies
8
Views
162
Replies
0
Views
731
Replies
4
Views
1K
Replies
2
Views
821
Replies
12
Views
665

1,190,807
Messages
5,983,026
Members
439,813
Latest member
monvarona

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back