Sumproduct but only first time corresponding value is seen e.g. un

Using XL03

Sample Data:

Name   Race Speed    Avg Speed  Track     Month
Joe         2:12                 2:15      OFCF      Jan
Joe         2:28                 2:15      ADL         Feb
Mary       2:14                 2:07      ADL        Jan
Joe         2:15                 2:15       ADL        Feb
etc

I already have sumproducts in place to count how many participants raced in 
each month, how many at each track, etc.

The last piece I need to add is the number of people for a given track on a 
given month, who had an /average/ time of over 2 minutes. But, I have 
repeats- the same person can run a track more than once in the same month, so 
their average comes across more than once and skews the total count.

Is there a straightforward way to only count the "unique" value combinations 
(first time for combo of person, track, month) and ignore all subsequent 
races by that person on the same track/month in the sumproduct count?

Thank you!
Keith 
0
Utf
3/18/2010 4:35:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
993 Views

Similar Articles

[PageSpeed] 42

Try this array formula** :

=SUM(IF(FREQUENCY(IF(D2:D5="ADL",IF(E2:E5="Feb",IF(C2:C5>TIME(0,2,0),MATCH(A2:A5,A2:A5,0)))),ROW(A2:A5)-ROW(A2)+1),1))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"ker_01" <ker01@discussions.microsoft.com> wrote in message 
news:702D94CF-902D-4E78-B1C1-840B24E827A4@microsoft.com...
> Using XL03
>
> Sample Data:
>
> Name   Race Speed    Avg Speed  Track     Month
> Joe         2:12                 2:15      OFCF      Jan
> Joe         2:28                 2:15      ADL         Feb
> Mary       2:14                 2:07      ADL        Jan
> Joe         2:15                 2:15       ADL        Feb
> etc
>
> I already have sumproducts in place to count how many participants raced 
> in
> each month, how many at each track, etc.
>
> The last piece I need to add is the number of people for a given track on 
> a
> given month, who had an /average/ time of over 2 minutes. But, I have
> repeats- the same person can run a track more than once in the same month, 
> so
> their average comes across more than once and skews the total count.
>
> Is there a straightforward way to only count the "unique" value 
> combinations
> (first time for combo of person, track, month) and ignore all subsequent
> races by that person on the same track/month in the sumproduct count?
>
> Thank you!
> Keith 


0
T
3/18/2010 5:20:42 PM
Reply:

Similar Artilces:

How can I check if an e-mail has been opened?
I would like to track the e-mails not opened when I do a mass mailing. Is there a way to track that in Publisher? No. You can't do that reliably in any application because there are many email programs. Even if they are using Outlook, you can tell your system to never send out a read receipt. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Jill" <Jill@discussions.microsoft.com> wrote in message news:71242736-B9A4-46EE-96E8-2D13CB0E4DD5@microsoft.com... >I would like to track the e-mails...

custom rules are applied first before junk filter
Hi I noticed that a custom rule of mine moving emails from an additional pop3 acct of mine to another folder seems to be applied before the junk filter, because any spam sent to that account ends up in the new folder rather than the junk mail folder. Also any spam going to the primary inbox account gets dealt with correctly. So it seems that any custom rules take precedence before the junk filter. Anyone else have this problem and if so anyone found a workaround. Thanks. Currently there is no workaround. It is a problem. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Aut...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

E-mails an hour off
All e-mails seem to be an hour behind the real time. No matter where they come from or what program they are all an hour behind. The Exchange 2K3 server is set to the right time and right time zone. My XP machine is the same, but if I send a message to myself via Outlook 2K3 it will come into my inbox an hour behind. What's going on? I just noticed this this morning. I ran a bunch of MS updates last night on all the 2K and 2K3 servers. Could an update be causing this problem? Any suggestion or idea would be appreciated. Mike check all clients and the server to see if they...

Why suddenly is British daylight saving time not being applied?
Suddenly today I noticed that my PC clock is wrong and running an hour fast and upon investigation it appears that the daylight saving time which should be being applied for my time zone, namely - (GMT) Greenwich Mean Time: Dublin, Edinburgh, Lisbon, London is not now being applied even though I changed nothing and the option [tick] Automatically adjust clock for saving changes is still ticked. At this time of the year (April to October) British clocks are put one hour forward, to GMT+1 or "British Summer Time" and usually Windows XP knows when that is, and normal...

Code Running At Design Time
Hello All I Use Access XP For Developng A Software But I Observe That When You Are Changing The Form Controls In THe Design View And Try To Save, The Compiler Will Execute Some Codes While Saving The Project. Does Any One Konw How Can I Prevent That Operation Thanks To All Can you provide more detail of what is executing? Is this the Timer event of a form, or what? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <999> wrote in message news:%23ESTaHbKIHA...

www.g ooglecrack.com
www.g ooglecrack.com ...

Clustered and stacked columns at the same time
Hi, I have a series of data that I would like to be represented on a single chart in clustered and stacked columns. I will explain in details the situation. I have sales data for the 4 regions, viz. North, East, West South. The data is additionally categorized across hardware sales and software sales. These data are grouped per month. Thus, I have a matrix as follows for each region: Period Hardware Sales Software Sales Jan xxx yyy Feb aaa bbb Mar ppp qqq .... .... What I wanted was to have a chart where the x asix is...

EXCEL97 FILE NAMES TRUNCATED TO THE FIRST LETTER IN THE FILE NAME
I use Windows XP home edition and Microsoft Office 97. Excel has suddenly started truncating the file name to the first letter of the file name. What do i need to do to correct the position? -- Geoff Check to see if Excel is running in Windows 95 compatibility mode. There was a post that said: When I disabled compatibility mode the file name problem went away. http://google.com/groups?threadm=020101c30916%2442320250%24a501280a%40phx.gbl And maybe this will help: HOW TO: Use Windows Program Compatibility Mode in Windows XP http://support.microsoft.com/default.aspx?scid=kb;EN-US;292533 ...

Remove first Exchange server
Hi, I've followed the Knowledge base article on removing the first Exchange 2000 server. Is it appropriate to shutdown that server for a day or so before uninstalling Exchange? I thought that if I've missed anything it may show up when the server is down. Thanks, John Always!! Give it as long as is practically possible in my experience and make sure people have utilized things like OAB etc without problems.... "John" <johnvan@ansonic.com.au> wrote in message news:%23aHDe8l0GHA.4972@TK2MSFTNGP03.phx.gbl... > Hi, > > I've followed the Knowledge ...

Formatting a cell for time
I have several thousand cells formatted for time as hh:mm:ss AM/PM. In the cell, the time is only showing as 09:00:00. I can get the AM or PM to appear only be entering the cell and pressing Return. Is there a quick way for me to do this on all cells (i.e., by a range)? Thanks! Hi format the cells with hh:mm am -- Regards Frank Kabel Frankfurt, Germany Maria wrote: > I have several thousand cells formatted for time as > hh:mm:ss AM/PM. In the cell, the time is only showing as > 09:00:00. I can get the AM or PM to appear only be > entering the cell and pressing Retur...

Show value in Data Table but not on live chart
This is a multi-part message in MIME format. ------=_NextPart_000_01CF_01C61A22.B78FC260 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am wanting to make a pivot chart that will show four data items in the = data table, and only show two of the items in the line chart. I have = found I can hide the lines, then I have to change and limit the Y axis. = If I change to a different pivot item, then I have to rehide the lines = again. Is there a simpler way to limit the lines in the chart and still = keep the four items in the data table...

Passing values to another form WITHOUT opening it?
Is it possible to pass multiple values to another form without opening the 2nd form? I have the OpenArgs procedure but it uses docmd.openform. See code below. What can I use to avoid the openform procedure? Private Sub cmdAdd_Click() On Error GoTo Err_cmdAdd_Click Dim stDocName As String, strArgs Dim stLinkCriteria As String strArgs = cboProjectNumber & "," & cboProjectName stDocName = "Child" If Not IsNull(cboProjectNumber) And Not IsNull(cboProjectName) Then DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , strArgs ...

E-mail in Calendar
Someone sent an unread e-mail into the Calendar folder. It shows up in 'folder veiw' as the calendar haveing an unread e-mail/message, but I can't find a way to view the e-mail. Is there any way that someone knows that I can get that message back to the inbox, or view it in the calendar as a message. Thanks in advance ...

Is there a way I can split last name first name in one cell
I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly......

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

How do I print only the first and last page of publication?
I need to print 500 11X17 sheets of only page 1 and 4 (first and last page) KJL <KJL@discussions.microsoft.com> was very recently heard to utter: > I need to print 500 11X17 sheets of only page 1 and 4 (first and last > page) Is this in a booklet format? Go to Page 1 File > Print > Current Page > Copies: 500 > OK > Print as separate booklet: No -- Ed Bennett - MVP Microsoft Publisher Print, current page, click "no". Try a sample first. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "K...

Comparing Time in a Formula
I am trying to do some conditional formatting which requires me to compare two "time" values". Basically what I'm doing is changing the text color if the value of a cell, which is formatted as a time of day, if that time is greater than 9:00 PM. In the conditional format I tried "Use a Formula to determine which cells to Format" with the formula "=$B3>9:00 PM" and several varients of the same general idea. 90% of them returned errors, the other 10% made everything true... even if it wasn't. The idea is that if B3's value were af...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

I want to print two copies at the same time of my report
I want to print two (2) copies at the same time of my modified report and it will have different print fields on the bottom of the report which are "Customer's Copy" and "Accountable". ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www....

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

Displaying time from 00:00 to 0.00
I want to do the opposite to what I have seen on the forums and convert time back to decimals ( I work on aspreadsheet but it has been passworded so I can't crack it. :) I want to be able to write (van out) (Van back in)(Total Van time) 7.25 11.25 4.00 Can anyone help me please ( or a way to crack passwords):cool: -- North for Short ------------------------------------------------------------------------ North for Short's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22695 View this thread: http://www.excelforum.com/showthrea...

Outlook 2007 DST/Time zone issue
I have a network with 4 computers running Office 2007, XP Pro connected to SBS2003. Outlook is connected to the Exchange 2003 component of SBS2003. Computers were deployed using a single, new image inside the last 3 months. New Zealand has had changes to the daylight savings change over dates, and as part of this process the required Microsoft Updates were deployed to the workstations, using WSUS, and applied to the server. Two of the computers have no problem, while two have an issue where calendar entries are 13 hours out. They appear to be defaulting to GMT, instead of NZT (GMT +1200)...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...