Hi
is there any way i can do the following:
on tab 3 i need to get the number of clients per month by business type by looking up the table on tab 1. however a client who has two transactions in the same month for the same business type only counts as one if you know what i mean?!
thanks
Try:
confirmed with CTRL+SHIFT+ENTER... copy across, change the [JAN] part to the respective column headers, and confirm each with CTRL+SHIFT+ENTER, then copy all down.=COUNT(1/FREQUENCY(IF(BUSINESS_SUBMITTED[BUSINESS TYPE USED]=BUS_PROT_TRANSACTIONS[[#This Row],[BUSINESS TYPE]],IF(TEXT(BUSINESS_SUBMITTED[DATE],"mmm")=BUS_PROT_TRANSACTIONS[[#Headers],[JAN]],IF(BUSINESS_SUBMITTED[CLIENT NAME]<>"",MATCH(BUSINESS_SUBMITTED[CLIENT NAME],BUSINESS_SUBMITTED[CLIENT NAME],0)))),ROW(BUSINESS_SUBMITTED[CLIENT NAME])-MIN(ROW(BUSINESS_SUBMITTED[CLIENT NAME]))+1))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC
This is unfair. I find your formula very intimidating impressive and totally clear as mud. WOW!!! I still have a LOOONG way to go! Well done Sir!
Please consider:
Be polite. Thank those who have helped you. Click the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Thanks, but it seems intimidating because of the way Excel 2007+ uses Structured Tables references (Explanation). It really isn't the best way to go when copying formulas across a table... as you see you need to manually adjust column headers... there is no "absolute references" as far as I know.. there are workarounds, but probably it's best to stick to the standard references using normal range and cell referencing as we are used to... and if needed dynamic named ranges for expanding/contracting columns....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC,
I tend to agree;
You had me very worried there for a while. Having seen such a formula for the first time, almost lead me to study to become a Janitor!it's best to stick to the standard references using normal range and cell referencing as we are used to... and if needed dynamic named ranges for expanding/contracting columns....
You have now just earned another gold star next to your name on my wall for people of NOTE!
Please consider:
Be polite. Thank those who have helped you. Click the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Hi,
this formula works fine throughout the month for each bus. type & month but at the end of the year, summing the months in the total column gives an incorrect value because the same client could have 2 or 3 transactions throughout the year. is there a different formula we can use for the total cell?
sorry...
Last edited by Loisw; 01-27-2012 at 01:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks