Gabriel_Pitt
(effective against the Irish)Registered User regular

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.

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.

0

## Posts

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

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?

Gabriel_PittonSo 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.

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.

Nod. Get treat. PSN: QuipFilter

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

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.

twitch.tv/kragaar