Help with Excel Formulas

Gabriel_Pitt

Due to shenanigans at work, I'm getting a lot more proactively detailed in tracking how much I'm owed for commissions, but I've never really had to get more complex with my spreadsheet formulas than '= simple math calculation.' If someone could help me out with I should write the formulas to achieve the below result, I would greatly appreciate it.

The bit where I need help is cell J2 is commission received, and K2 is commission outstanding. Column L is a Y/N variable for whether I've been paid or not. So the formula should be something like =IF(L# "Y"[L#+J2],[L#+K2]). I'm not sure how to make those values total up in the appropriate cells. Use SUMIF in some capacity?

Any assistance is appreciated.

Dr. Frenchenstein

wouldn't you just want a column for "commission" and then another column for what you've received? so instead of having to move values from K2 to J2 as you are paid, you can just have the formula pop your commission into the paid column when you change L to "Y".

or do you get partial payments you are trying to track as well?

Gabriel_Pitt

Looks like I was over-thinking things a bit, because you're right, all I really need is is the total commission I've earned, and how much I've actually received. The first is just a simple sum formula, so the second is what I still need to work out.

Continued fiddling with the formulas makes me think what I need to do is just have something in J2 like =IF(L9:L500="Y",SUMJ9:J500), with a function to add up every one which returns a Y. Can anyone tell me if I'm in the ballpark?

MrTLicious

There's a function called "SUMIF" that you'll want to use.

So the function you want is something like:

=SUMIF(L9:L500,"Y",J9:J500)

The first range tells you where the conditional is. Here, you only want to sum things that have been paid (i.e. have a cell value of Y)

The second argument tells Excel whether to sum. In this case you want to sum just the ones where the conditional is exactly Y.

The 3rd argument states what cells you want to sum. Here, we're not summing the Ys (that doesn't make sense) we're summing the analogous dollar values which I assume are in column J. In gerenal, you can leave this blank if your conditional range and sum range are the same.

DevoutlyApathetic

That sounds good.

You'll probably want to include dates earned in that list for the accounting department and break points "You owe me x for commission that are 30 days old, you owe me y for commission that is 60 days old" and so on.

Gabriel_Pitt

That's part of what I'm doingwith all those other coluulmns.

And sumif is the function I was looking for. I know a little more about Excel than I did a moment before. Thanks all!

localh77

Sounds like you got it. Just a tip that what I like to do is sum the entire column. So instead of =SUMIF(L9:L500,"Y",J9:J500), I use something like =SUMIF(L:L,"Y",J:J). That way when the day comes that you go to row 501, you don't have to remember to update the formula. And if you're ever copying and pasting formulas, Excel likes to auto-update the pasted formula based on where you moved it.

Kragaar

localh77 wrote: »

Sounds like you got it. Just a tip that what I like to do is sum the entire column. So instead of =SUMIF(L9:L500,"Y",J9:J500), I use something like =SUMIF(L:L,"Y",J:J). That way when the day comes that you go to row 501, you don't have to remember to update the formula. And if you're ever copying and pasting formulas, Excel likes to auto-update the pasted formula based on where you moved it.

And just to be anal, put in a little reconciliation at the end so you know you've captured everything. Just have a sumif for Y and a sumif for N, total that, total the entire column of data and subtract the two totals so you can see if it equals zero. Since sumif is very literal if you type in "Y " instead of "Y" or some other garbage value that isn't exactly Y or N it won't get counted and may get missed if the amount of information is too large.