Excel 2007 : Calculating Total Commissions

First I thank you for reading this! I've searched quite a few things on this forum and found great answers. This one is stumping me because it is too general I think so I can't search for an answer.

I've attached a sheet of what I want to do.

I have exported from QB a report that gives me the Sales Category, Employee and Amount as shown.

I'd like to be able to calculate the commission per employee per sales category. Right now I've just taken the Amount cell and multiplied by the commission. I think there is probably an easier way but this works for the moment.

The info in the box is really what I'm trying to do.

After the commissions are calculated I want to create a list of employees and their total commissions.

The list is somehow like - if there is a name in D (because there will be blank spaces) add it to the list - taking into account duplicated names.

Then take the amount in J for each D and add it up.

It isn't complicated but I just don't know how to do it or what to search for.

Any and all help is greatly appreciated!

Thanks in advance.

The simplest solution...

Delete columns C, E, F, H & I if indeed they are blank - if you can't delete them type an "x" into C2, E2, F2, H2 & I2

Now highlight B4:B12 -> press F5 -> Special -> Blanks -> OK
Now type = and then up arrow and press CTRL + ENTER

Now highlight B2:E12 (or B2:I12 if you couldn't delete the columns) -- > go to Insert Tab on Ribbon and select Pivot Table -> when prompted click OK.

You should now be on a new sheet... with a Pivot Table Field List visible on right hand side of your screen...

Select Employee and drag it to Row Label area,
Select Sales Category and Drag it to Column Label area
Drag Commission (and Amount if desired) to the Values area and ensure they are set to SUM
(to do this if not default click on the item and select Value Field Settings choose SUM and click OK)

Is that what you want ?

(it may seem like a bit of a fiddle but once you get the hang of the setup process you'll be generating Pivots in next to no time - remember you can double click into any result value and get a transaction listing of all items that make up that result)

Ah, the joys of QuickBooks - I don't miss it.

Highlight any columns you need to apply this to -> CTRL + 1 -> via Number Tab set Format to General click OK

Now apply the blanks approach

essentially the cells are formatted as Text meaning when you enter the formulae XL treats it as a text string and it won't update correctly in this context even if you subsequently coerce it back to formula - you must correct formatting first

Yep, that's it!

And since you've been so helpful - the middle step that I did manually is bugging me now.

There must be a way to calculate the different commissions besides putting in the formula in each cell.

Again I've attached another sheet. I need the commission column to be filled in according to the commissions that I added.

Not really sure what you're looking to do ... you can highlight I4:I12 and run the SpecialCells route - once the Blanks are selected type in

=$G4*VLOOKUP($B4,$K$3:$M$5,3,0)%

and press CTRL + ENTER to commit to all cells simultaneously

Remember, on your PT (if you haven't already), to uncheck "blank" option from the Employee field else you'll get some undesired commission figures in the resulting table.