# SUMPRODUCT Multiple criteria top/bottom n values

1. ## SUMPRODUCT Multiple criteria top/bottom n values

I have been going round in circles with this for a while now....

I have a table as follows;

A1 ProjectDate ClientName ProjectDuration
A2 22-Sep-15 Client 1 1:56:20
A3 22-Sep-15 Client 2 8:33:21
A4 22-Sep-15 Client 3 2:45:55
A5 22-Sep-15 Client 4 16:22:01
A6 22-Sep-15 Client 5 1:00:00
A7 22-Sep-15 Client 6 23:43:21
A8 30-Apr-15 Client 1 6:07:54
A9 30-Apr-15 Client 2 8:33:21
A10 30-Apr-15 Client 3 0:33:21
A11 12-Feb-15 Client 6 18:00:21
A12 12-Feb-15 Client 1 6:43:05
A13 12-Feb-15 Client 2 9:32:02
A14 12-Feb-15 Client 3 10:22:32
A15 12-Feb-15 Client 4 1:22:45
A16 12-Feb-15 Client 5 10:44:02
A17 01-Jan-15 Client 6 4:30:00
A18 01-Jan-15 Client 1 12:10:02

And I want to know the average of the quickest n projects on a given date. The data columns are named ProjectDate, ClientName and ProjectDuration to avoid complicated range names in the formulas.

Cell E1 contains the date that I am running the formula on. So say E1=22-Sep-15 for this example and I want to find the average time for the fastest 3 projects started on that date.

My logic is;
Step 1 get a list of all the projects on 22-Sep-15
Step 2 add together only the fastest 3 times
Step 3 divide the result by 3 to get the average time

=SUMPRODUCT(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3

However the result is always 0

Interestingly if I flip the requirements of the formula to "find the average of the SLOWEST 3 projects on 22-Sep-15" using the following formula;

=SUMPRODUCT(LARGE((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3

It works absolutely fine giving the result 16:12:54.

It may be possible to do the same using AVERAGEIF. AVERAGEIF(ProjectDate,E1,ProjectDuration) will return the average project time of every project on 22-Sep-15 but I have not been able to make this work with the extra condition of the fastest 3 projects.

Any help would hugely appreciated. I have been trawling the internet for a few days now trying to figure out a solution.

Thanks.

2. ## Re: SUMPRODUCT Multiple criteria top/bottom n values

You might find it helps to add ProjectDuration<>0

Regards, TMS

3. ## Re: SUMPRODUCT Multiple criteria top/bottom n values

Did you tried this..

Formula:
`Please Login or Register  to view this content.`

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

4. ## Re: SUMPRODUCT Multiple criteria top/bottom n values

Perfect solution;

=SUMPRODUCT(SMALL(IF(ProjectDate=E2,ProjectDuration,"a"),{1,2,3}))/3

Thanks.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1