Calculating 2007 Commissions vs 2006?

[DukeVBall]

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.

Thanks in advance.
dukevball @ gmail.com

 

[Scott Huish]

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]

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]

debooo:
How would that sum anything?

 

[debooo]

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.

 

[PaddyD]

or use a pivot table