Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Functions

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Functions

Resolved · High Priority · Version 2003

Reply Thu 27 Aug 2009, 16:23Delegate Olufunke said...

Olufunke has attended:
Access VBA course
Excel VBA Intro Intermediate course

Functions

how to do unique counts

For upcoming training course dates see: Pricing & availability

Reply Fri 28 Aug 2009, 10:18Trainer Stephen said...

RE: functions

Hi Olufunke

Thank you for your question

Could you please clarify the following for me

1. What is it you are counting?
2. Are you working in Access or Excel

Thanks

Stephen

Reply Thu 19 Nov 2009, 09:39Trainer Rich said...

RE: functions

Hi Olufunke,

This question has now been left unattended for a while.

We like to keep down the large number of active forum posts we receive.

As we are waiting for your response for further information to help resolve your issue, we will be automatically marking this question as resolved in the next 5 days (unless you post a follow-up within that time).

We look forward to hearing from you.

Thank you.
Regards, Rich

Reply Thu 19 Nov 2009, 21:54Delegate Olufunke said...

RE: functions

The count is in Excel

1. A client might have more than one records
2. I might filter for certain parament,, based on that parameter I also want to do a unique count on the remainder of the records

Hope this makes sense

Regards
Funke

Reply Sun 22 Nov 2009, 16:07Trainer Stephen said...

RE: functions

Hi Olufunke

Thanks for the update

If I understand correctly you want to filter your record on a certain criteria (e.g. Client Name) and then count how many records there are that match this. You then also want to know how many additional records there are.

This is quiet a complex thing to do in code, but the general strategy would be to:

1. Use the count method of the rows collection of the current region to determine how many records there are in total, and assign this to a variable (intTotalCount)
2. Use the filter method of the selection object to filter the data. (I suggest recording a macro to see the code for this)
3. Then repeat step 1 for the filtered data saving that to a variable(intFilterCount)

IntFilterCount will give you the number of items in the filtered data and that less intTotalCount will give you the balance

If you have any problems please do follow up to my post

Regards

Stephen

Mon 30 Nov 2009: Automatically marked as resolved.

Excel articles

· How Teachers Are Using Microsoft Excel To Enhance Learning in the Classroom
· Excel Course Tips and Tricks to Liven Up Your Excel Spreadsheets
· Advanced Excel and PivotTable Reports
· Using Find And Replace In Excel
· Excel 2010 - Is It Worth The Upgrade?

Please browse our web site to find out more about
excel consulting and other Microsoft training courses.

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.

View all Excel hints and tips