promblem counting individual data between various ranges

[ShonaM]

I’ve been viewing the forum for some time and I can see that there are lots of questions around similar issues but I am struggling to find a solution that matches what I need.


I analyse attendees on courses for a client. I need to provide them monthly on the number of individuals that received training on particular vendors' courses within each month timeframe. Its sounds simple but I am struggling to do this without fiddling around with deleting duplicates each month – I’d ideally like one formula that can calculate this for me. I have no issue in using a Countifs function to count the number of attendees between the various criteria of course Vendor and Date range, but can’t work out how to produce a calculation for the number of “individuals” on the same criteria.


So, for example my data looks like:

Course title Course Vendor Attendee event start date

Leadership company 1 John 01/11/2011

Leadership company 1 Sally 01/11/2011

excel company 2 Peter 09/10/2011

excel company 2 Richard 09/10/2011

Word company 2 John 08/11/2011

powerpoint company 2 Paul 03/10/2011

Word company 2 John 12/11/2011

powerpoint company 2 Richard 03/10/2011

Time Management company 1 John 09/11/2011


and, I want to produce the answer “2” to the question – “how many individuals received training by Company 1 in Nov?” (John and Sally).

and

The answer “3” to “how many individuals received training by company 2 in Oct” (Peter, Richard, Paul)

(dates in the above are UK type dd/mm/yy)


The names in the real data vary each month (I actually calculate on a payroll number (Alphanumeric) so that I can easily identify individuals), and the data is very lengthy, so I can’t put in a calculation whereby I am stating the names that I am calculating on.

Any help would be greatly appreciated!

 

[Kevin Lehrbass]

Hi ShonaM,


Try this formula: =SUMPRODUCT((B2:B10="company 1")*(H2:H10=11))

Column H contains a formula to extract the month number from start date for each row: =MONTH(G2) (drag down for all rows)

Column B has your company names.

If this works, you can type company 1 into a cell (let's say J4) and do this:

=SUMPRODUCT((B2:B10=j4)*(H2:H10=11))

Repeat this for the month number if you like.

Cheers,

Kevin

 

[ShonaM]

Thanks Kevin, I'll give it a try.

 

[ShonaM]

Hi Kevin, thanks for the formula. That will give me a count of how many people attended over the given period, but if one person attended 2 courses it counts them twice,and what I need is a formula that recognises a duplicate and does not double count them. So if john attendes 2 courses in Nov, even if they are different dates etc i only count John once.


Still stuck I think, but many thanks for your help.

 

[NARAYANK991]

Hi ,


Are your queries going to follow the pattern you have mentioned ? Will it always be a combination of Company and Month , for which you will need to know :


1. The names of the attendees

2. The number of attendees


Narayan

 

[ShonaM]

Hi, yes. that's correct, its alway a combination of company and month. I don't need a list of names, its just the total number of people (attendees) in a given month that have received training by a particular trainig company.

 

[NARAYANK991]

Hi ,


Can you check out the uploaded worksheet ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21119


Copy the entire address and paste it in your browser.


I have changed some of the data from yours ; there are some queries given , and the formulae for getting the answers to those queries ; all the formulae follow a certain pattern ; you can make your own queries , and change the formulae according to the pattern , and see if you get the right results.


I got these formulae from the following link :


http://www.get-digital-help.com/2011/07/12/count-unique-distinct-values-that-meet-multiple-criteria-in-excel/


Narayan

 

[ShonaM]

Narayan, that works perfectly! Many thanks.