Vlookup and sum without repeating

Dear all,

I have a list of data

name   Days   Car
John   5      Red
Paul   4      Blue
Paul   2      Red
Mike   2      Green

I want to write a formula which would add up all days for a person without overlapping i.e. Paul in row 3 would come out at 6 and zero in row 4. I could put filters on, sort the data etc but was wondering if a formula exists?

Thanks
Eoghan


EggHeadCafe - Software Developer Portal of Choice 
Fun With OPML in ASP.NET
http://www.eggheadcafe.com/tutorials/aspnet/c8a91ecd-2d44-4576-aef0-bf3db36b3ee5/fun-with-opml-in-aspnet.aspx
0
eo
10/31/2009 8:43:18 AM
excel 39879 articles. 2 followers. Follow

1 Replies
351 Views

Similar Articles

[PageSpeed] 17

With the data in columns A:B, I could use this formula:

=if(countif(a:a,a2)>1,0,sumif(a:a,a2,b:b))



eo, Sull wrote:
> 
> Dear all,
> 
> I have a list of data
> 
> name   Days   Car
> John   5      Red
> Paul   4      Blue
> Paul   2      Red
> Mike   2      Green
> 
> I want to write a formula which would add up all days for a person without overlapping i.e. Paul in row 3 would come out at 6 and zero in row 4. I could put filters on, sort the data etc but was wondering if a formula exists?
> 
> Thanks
> Eoghan
> 
> EggHeadCafe - Software Developer Portal of Choice
> Fun With OPML in ASP.NET
> http://www.eggheadcafe.com/tutorials/aspnet/c8a91ecd-2d44-4576-aef0-bf3db36b3ee5/fun-with-opml-in-aspnet.aspx

-- 

Dave Peterson
0
petersod (12005)
10/31/2009 1:13:26 PM
Reply:

Similar Artilces:

Vlookup edited
Hello all you wonderfulhelp, Is it possible to avoid "NA" when using "vlookup" function. I need info only where it brings results. Thank you -- smile =IF(ISERROR(VLOOKUP(B1,C1:D4,2)),"",VLOOKUP(B1,C1:D4,2)) -- Gary''s Student - gsnu200851 Example of using ISNA rather than ISERROR which hides all errors. =IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE)) Gord Dibben MS Excel MVP On Thu, 7 May 2009 09:26:02 -0700, israel <israel@discussions.microsoft.com> wrote: >Hello all you wonderfulhelp, > >...

How to Clear or Reconcile without right-click
How does one Clear or Reconcile the currently selected transactcion without going through the tedium of right- clicking and selecting Mark As -> Cleared (or Reconciled)? Thanks CTRL-SHIFT-M "MoneyUser" <anonymous@discussions.microsoft.com> wrote in message news:1a72401c41db7$b7003000$a101280a@phx.gbl... > How does one Clear or Reconcile the currently selected > transactcion without going through the tedium of right- > clicking and selecting Mark As -> Cleared (or Reconciled)? > > Thanks In microsoft.public.money, MoneyUser wrote: >How does one Cl...

Printing without certain text (i.e., answers, notes)
Does anyone know an easy way to print two versions of a document? For example, I need to print a student version without answers and instructor's notes. Thanks, TM One way would be to use a style (call it Answers for example), when you want to print out the students copies change the color of the style Answers to white. That should do it. "TM" <t_mccollough@yahoo.com> wrote in message news:e6czHS2YDHA.1384@TK2MSFTNGP10.phx.gbl... > Does anyone know an easy way to print two versions of a document? For > example, I need to print a student version without answers an...

Sums and percentages in reports
I have the following field in report's detail area: =Count([Cause]=1) (where 1 refers to a row of a table's dropdown) at the report footer I have the following sum for that column: =Sum(Abs([Cause]=1)) with the Runnin Sum - Over All. The calculation comes out correc so I'm assuming the formulas are correct. However, when I try to get a Percentage by deviding the field formula with the footer formula I don't get the correct answer. Can someone help me with this formula? You have told us what you attempted but it isn't clear what you want to accomplish. Your expres...

video streaming in intranet , without buffering
Hi, I am trying to bulid an application in VC++ .NET in which a server will send data for video files that contains audio/Video or both. and at cient side i want the video to be played back without buffering. the communication is unicast and in a Intranet, NOT internet. plz tell me where to start and how to go about it regards ...

Send email without read access for queues
I got the problem that a user should be able to send emails with the webclient but he should not be able to see the defined queues. Thats not possible. In my opinion it does not make sense at all that you need read privileges on queues to send emails out of the web client. That should be changed. My suggestions are: 1. Let users send emails when they have the privilege to create activities. or 2. Define a new privilege for sending emails AND (I find that one pretty important) 3. Show only those items in the queue that a user has at least read privileges for. In my case the user doe...

.NET Classes: Visio Pro without VS
Hi, I own Visio 2007 Professional, but I do not own Visual Studio -- I only have the Express editions. Because of that, I cannot reverse engineer from Visual Studio to get .NET classes into Visio. How can I get .NET classes into Visio Pro 2007 without the functionality of an entirely separate product? Thanks, John Unfortunately, you have no way to do it in Visio 2007. You will need to have visio 2003 for enterprise architect, reverse and use it in Visio 2007 Enjoy with Visio. <jar349@gmail.com> a �crit dans le message de news:7066a237-0f2d-464b-ba43-5ad053e8dcb4@d4g2000prg.googleg...

Using IBF without CRM license
Hi there! Can a user who hasn' t a CRM license use the IBF (to see info about a CRM entity) when working an Office Application? Thanks, Elena Not sure of the "official" answer, but my guess is no. The IBF adapter for CRM is going to piggy back on the CRM API's. The CRM API licensing requires a license. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 9 Feb 2005 12:32:15 +0200, "Elena" <newsgroup@sieben.gr> wrote: Hi there! Can a user who hasn' t a CRM license use the IBF ...

vlookup problems with left
I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit number (last digit is some kind of control digit) and when i use left to take only the first 9 digits =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE) i get #N/A if i manuallly delete the 10th digit i get mached data. is there a problem with using left on vlookup? =left() returns text. That will never match a real number. I'd take the integer amount after dividing by 10. =vlookup(int(f200/10);w198:w201;1;false) But if you're really only looking to see if there's a match, you could use: ...

vlookup help linking data between worksheet
hi, I have a master list of students (about 200+ )in one worksheet. On the succeeding worksheets are the attendance for seminars. We have more than 20 seminars in a year. Because of the large no of attendees per seminar, I usually type out a list of the students who came for each seminar, so there will be 20 +attendance worksheets. Not all students will come every time and there are new ones for each session. To update on master list, I will sort each sheet by surname, print it out and type in separate column (date) for each session and typed "P" for present and &quo...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Summing multi-level bill of material
All, I have a multi-level bill of material and I want to create a function that will sum it properly without having to sum up each level manually. Here is an example: Level Item Quantity Cost 1 Chair 1 $39 2 Seat 1 $15 3 Cushion 1 $10 3 Base 1 $5 2 Leg 4 $1 2 Back 1 $20 3 Leather 1 $12 3 Wood 1 $8 In my example, I have a chair. The chair is composed of a seat ($15), four legs ($1 each), and a back...

Multiple "SUM IF" functions in one formula??
I know it can be done, Excel can do anything but I'm not sure if I can even explain it. For example, I want column D2 to display a qty IF a customer number equals a specific value AND a part number equals a specific value. Basically Column A is a list of part numbers, and Row 1 is a list of customers. I need to pull how many parts each customer has ordered. Hi see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Dax" <dax.tipton@smc.com> schrieb im Newsbeitrag news:593e3ca7.0408301337.7b8f6a35@posting.google.com... > I ...

Sum of all formulas in one column?
Is there some automatic way to make Excel total all of the cells containing formulas in a column? I know about the Subtotals feature, and being a new user, I can't quite figure out how to work it. I got to thinking if there is some easy formula that SUMs the results of all formulas in the same column, that would do exactly what I want it to do. Right now, all I'm doing to create a grand total is creating a formula manually to give me my grand total, i.e. =SUM(C5,C11,C18). However, if I'm going to add rows with subtotals in between each of my categories, I'll have to manu...

OWA logon without domain name
Is there a way to set up the OWA logon so that users can either type "domain\username" or type "username" to log on? We used KB article 903942 and Forms-Based Authentication to allow users to log on simply as "username" -- but people who had already been trained to enter "domain\username" are then blocked. Any ideas? Thanks very much in advance. Ron Proschan ...

Insert rows and paste without clearing constants
I need to Insert Rows and Paste the selected row a specified number of times per an input box value. I am using the following code as a start and have commented out the lines to clear the constants and that works fine except the autofill portion increments the constant values. Thanks in advance for any help. This is how I need the result to be: Room Part# 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 This is what I am getting: Room Part# 6 WN1B-24 7 WN1B-25 8 WN1B-26 9 WN1B-27 10 WN1B-28 11 WN1B-29 Sub CopyRows() ' Documented: http://www.mvps.org/dmcritchie...

Copy and Paste from One Document to another without changing forma
Everytime I copy and paste from one publisher 2003 document to another the formats change after I paste it to a new document. Then I need to go to the new document and change all the formats for each text box so it includes all of the text in the original tex box. It defaults to something other then what was pasted from the original document. How can I change it keep the same as what I originaly copied dltgng wrote: > Everytime I copy and paste from one publisher 2003 document to another the > formats change after I paste it to a new document. Then I need to go to the > new do...

VLookup?????
=IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in tableB) This is the code i have. I want to search tru all column B in table A. If i find an identical value in table B i want the field in table A to show the corresponding field in Column A in table B. Also if there is 2 occurences of column B in table B, i want the field in table A to show both values. Any ideas? (if u can understand what im asking) -- Hazy ------------------------------------------------------------------------ Hazy's Profile: http://www.excelforum.com/member.php?action=g...

Formulas - SUM
Hi, Is there a version of the SUM formula that can be used to capture rows/columns added outside the existing formula range? Thanks in advance to anyone who can assist. -- Carla Please check your previous post. You dont need to multi-post.. -- Jacob "Carla" wrote: > Hi, > > Is there a version of the SUM formula that can be used to capture > rows/columns added outside the existing formula range? > > Thanks in advance to anyone who can assist. > -- > Carla ...

Dates without //
I need to convert a column of dates formatted "yyyymmdd" t "yyyy/mm/dd". It seems like it should be easy, but it eludes me. Thanks! Kathi -- Message posted from http://www.ExcelForum.com Excel is most likely not recognizing your data as dates and thus you can't reformat them. Convert them to recognizables date with: =TEXT(A1,"0000\-00\-00")*1 and then format as the formula cells as yyyy/mm/dd. HTH Jason Atlanta, GA >-----Original Message----- >I need to convert a column of dates formatted "yyyymmdd" to >"yyyy/mm/dd". It ...

Sum same cell in many books
I want to create a new workbook that sums the cell H22 in every book that is in a certain folder. Is there a way to do this? This folder will have a new book added every day .. Thanks, joe Hi honyacker You can create a Sum workbook with code. See http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "honyacker" <invalidemail@hotmail.com> wrote in message news:ug7uBPz$FHA.1568@TK2MSFTNGP10.phx.gbl... >I want to create a new workbook that sums the cell H22 in every book that is in a certain folder. > Is there a way to do this? T...

Copy mailbox to a PST without losing mailbox contents
I have a user that will be let go on Friday and i need to copy the contents of their mailbox to a PST file, how can i do this without destroying the data? I tried exmerge but it doesn't just copy it starts to empty the folder as it goes, i can't let this person know i am doing this. You can setup a mail box and user and a PST file on another system login as that user (i asume its an OST rigth now) when the personal folder is set up just copy what you need as in in box contacts ext... then copy the pst onto a disk and delete that mailbox off that system. the user will never k...

How to open a form without show it in TaskBar
Hi. I would like to know if it's possible to open a form in access without show it in task bar. I know that it's possible in Visual Basic. Is it possible in Access? Regards, Marco Marco, In Access 2003, Tools >> Options >> View tab - uncheck the box for Windows in Taskbar. Jeanette Cunningham "Marco" <Marco@discussions.microsoft.com> wrote in message news:E492D97E-02AA-4E15-BF80-E71605DA0D57@microsoft.com... > Hi. I would like to know if it's possible to open a form in access without > show it in task bar. > > I know that it's ...

SUM IF Return Max Value
Hello there, after a day of starring at it I'm convinced I need a little help. I was looking for a way to return the highest value (of Collumn B) to B3 that matches the criteria (name) stated in A3. The database (in another sheet) consist of 11 different names (in Collumn A) which all have 5 different values in Collumn B. This makes 55 rows. Thanks in advance! I am sure I haven't got it as i DON'T UNDERSTAND YOUR QUESTION FULLY. =MAX(if(SHEET2!A1:A55=A1,Sheet2!B1:B55) which is an array formula so commit with Ctrl-Shift-Enter If wrong, post some data and expected results...