Quick Links ▼

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

Forum home » Delegate support and help forum » Microsoft Excel Training and help » COUNTIFS function combined with array?

	98.1 % recommend us
	More live stats...

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

Training information:

COUNTIFS function combined with array?

Resolved · Urgent Priority · Version 2007

Reply Tue 29 Mar 2011, 12:53Delegate Paul said...

Paul has attended:
Excel Advanced course

COUNTIFS function combined with array?

I have created a reporting database for competition results. I have successfully used the COUNTIFS function to provide reports based on several filtering criteria (i.e. number of gold medals, number of silver medals, number of bronze medals, number of medals at senior competition, number of medals at junior competition etc etc..). Having completed the excel training I am lookign to recreate tables using Pivot Tables

However, for each of the summary report figures I need to account for how many individuals are responsible for these (e.g. the report may show 10 gold medals but 5 athletes may have been responsible for these). This is something that I don't believe that a Pivot Table can calculate so I am looking to include some sort of worksheet function that will effectively count the number of unique name entries for each of the report filters applied.

I wrote a fairly complicated array that counts the number of unique name entries that appear in a column (where "name" refers to column B that contains names First_Last format.

The array formula is:
=SUM(IF(FREQUENCY(IF(LEN(Name)>0,MATCH(Name,Name,0),""), IF(LEN(Name)>0,MATCH(Name,Name,0),""))>0,1))-1

While this successfully gives me the number of unique names that appear, I have failed to combine this with the other COUNTIFS functions to calculate number of athletes responsible for each of the competition summary report fields.

Many thanks in advance!!

For upcoming training course dates see: Pricing & availability

Reply Wed 13 Apr 2011, 11:09Delegate Paul said...

RE: COUNTIFS function combined with array?

Hello - any ideas where to go on this?

Thanks

Reply Wed 13 Apr 2011, 15:29Trainer Jacob said...

RE: COUNTIFS function combined with array?

Hello Paul

Thanks for your post, I have checked with two of the team and they both can't find any resolutions to this.

There might be an answer but it is beyond the scope of this forum. If you would like to take this further we can have a look at your files and let you know how much development time it will take and any related costs.

Just pop me an email if you want to look at this further.
jacob

Kind regards

Jacob

Wed 20 Apr 2011: Automatically marked as resolved.

Excel articles

· Excel Brings You The Tools To Spice Up Your Spreadsheets
· Should I Use Data Bars, Colour Scales Or Icon Sets In Excel?
· Discover The Top Ten Changes In Excel 2010
· Baffled by custom number formatting in Excel?
· Jazzing Up Excel With Graphics

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

Excel tip:

Recently used file list

Under the File menu, you may find a list of files at the bottom of the menu. These files represent the most recently used Excel spreadsheets. This file list provides a quick way for you to access your files.

You can disable the file list feature of Excel. This is done by

1. Choose Tools > Options menu. You will see the Options dialog box.
2. Ensure the General tab is selected.
3. Make sure the Recently Used File List check box is cleared.
4. Click on OK.

View all Excel hints and tips