What is the best approach to a commission report.. vba/formula/chart/table?

I wonder if someone can help me get started with a new task. I need to make a commission report for field sales reps easily available, it would need to do the following;

. Selectable Calendar month, including progress of current month. from column b
. Select between salesperson from column s

. Show a count of records showing a "4" in column w
..Total value in column q for these records

.Show a count of records showing a "8" in column w
..Total value in column q for these records divided by 2

.Show a count of records showing a "12" in column w
..Total value in column q for these records divided by 3

.Total these three figures for gross production.
.Check each record for any value in columns ac-al
..Recalculate above using only these records for net commission.



I hope that makes sence with the attached wb. Im trying to be quick as im rushing out. Would imagine this would be easier using formulas but I wouldn't know where to start. vba Im pretty sure I could do it, but dont think Id choose the right methods.

Could somebody point me in the right direction?

Originally Posted by Richard Buttrey

Hi
Have you tried a pivot table?

I'm assuming that each row contains records similar to row 3 and not what appears to be nonsense data in rows 4-6

Sorry about that I was rushing and didn't realise there was a filter active. The numbers were just used for me to test a userform.




Honest answer Richard, I don't fully understand what a pivot table is! but thats my job to learn, if you think it would be a good approach Ill do some reading. Thank you

Originally Posted by Richard Buttrey

Hi,

See attached

I added some additional rows in the data. Above the pivot table you can see the drop down that will allow you to select 4, 8, 12 or indeed any other number from column W.

Pivot Tables are immensely powerful when you need to summarise and analyse data from a 2 dimensional database. Extremely easy to set up, just a few clicks. A little more effort needed to fully get to grips and the full functionality available from them but if you do persevere you'll use PTs to analyse data in ways you've not yet dreamed of.

Thank you very much, these will be very useful and seem easy to set up. Im assuming you can query a pivot table using vba and formula?

I need a little more functionality (ie mark a column of filtered records etc) maybe a combination of formula and vba might be right. Ill create and upload a template of what Im trying to achieve soon.


Thanks again

1212_2.xlsmI have created a template for what Im looking for. I could write vba for it but it would be pages and pages of finds and filters.

Now I hope its a little more obvious what Im going for. Thank you for all the help Richard

Originally Posted by Richard Buttrey

Hi,

I've given you a starter for 10 with the attached.

I wasn't sure which column was the Sales persons name but no doubt you can change as necessary. You'll need to decide the definition of a week to complete the deals / week.

Anyhow see how you get on.

Exactly what i needed to get started nice one.. Ill report back soon

Thanks again buddy

Im getting there. I have managed to duplicate what you have given me on the live document. I have to be honest however, I hit a brick wall as soon as I try to go a step further.

I need to duplicate this formula
=COUNTIFS(dates,">="&DATE($F$7,$A$6,1),dates,"<="&EOMONTH(DATE($F$7,$A$6,1),0),salesreps,$F$6)
But only reference records with a value in either or both of the following columns AC AD

Seeing how this would fit into this formula will help me understand how its constructed.

Thanks again for the help

All sorted (named range set up wrong)... Got a nice report now exactly what I wanted, just need to write some vba to mark the records com paid and job done.


Thanks for the help Richard, was exactly what i needed.