formula of sum and text

In column "A" (A1:A20) titled "Employees", there's recorded employee names 
as work is done through a weeks time.  There are ten different names that 
are possible.



In column "B" there are empty cells (B1:B20) titled "Miles".  When an 
employee logs miles on a day it is registered in column "B" adjacent to 
their name.  There are only two employees per sheet that will have recorded 
miles, although it is not always the same two.



In cell "C1" & "C2" there are empty cells titled "Employee Miles".  We 
manually record the two employee names that have miles on that particular 
sheet in cells "C1" & "C2".



In cell "D1" & "D2" there are empty cells titled "Miles Total".  We manually 
count the miles each of the two employees have recorded in different cells 
in column "B" through a weeks time and type the totals in cells "D1" & "D2".



In the cells in column "C" we wish to have the names automatically appear 
based on if that particular name had any mileage recorded in column "B".  In 
the cells in column "D" we wish to have the totals automatically appear 
based on the sum of miles in column "B" for that particular employee.



How may we automate entries in "C1" & "C2" and "D1" & "D2"?



Regards,

Gary


0
allge (63)
5/3/2007 4:57:51 PM
excel 39879 articles. 2 followers. Follow

3 Replies
242 Views

Similar Articles

[PageSpeed] 39

If I understand .....

...........A..........B
1......Tom.......10
2......Bob...........
3......Joe.........10
4......Lou...........
5......Tom.......10

You want Tom and Joe extracted and the total of their miles logged?

Enter this array** formula in C1:

=INDEX(A1:A5,MATCH(TRUE,B1:B5<>"",0))

Enter this array** formula in C2:

=INDEX(A1:A5,MATCH(1,(B1:B5<>"")*(A1:A5<>C1),0))

Enter this formula D1 and copy to D2:

=SUMIF(A$1:A$5,C1,B$1:B$5)

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Gary" <allge@cox.net> wrote in message 
news:e$INPiajHHA.4772@TK2MSFTNGP05.phx.gbl...
> In column "A" (A1:A20) titled "Employees", there's recorded employee names 
> as work is done through a weeks time.  There are ten different names that 
> are possible.
>
>
>
> In column "B" there are empty cells (B1:B20) titled "Miles".  When an 
> employee logs miles on a day it is registered in column "B" adjacent to 
> their name.  There are only two employees per sheet that will have 
> recorded miles, although it is not always the same two.
>
>
>
> In cell "C1" & "C2" there are empty cells titled "Employee Miles".  We 
> manually record the two employee names that have miles on that particular 
> sheet in cells "C1" & "C2".
>
>
>
> In cell "D1" & "D2" there are empty cells titled "Miles Total".  We 
> manually count the miles each of the two employees have recorded in 
> different cells in column "B" through a weeks time and type the totals in 
> cells "D1" & "D2".
>
>
>
> In the cells in column "C" we wish to have the names automatically appear 
> based on if that particular name had any mileage recorded in column "B". 
> In the cells in column "D" we wish to have the totals automatically appear 
> based on the sum of miles in column "B" for that particular employee.
>
>
>
> How may we automate entries in "C1" & "C2" and "D1" & "D2"?
>
>
>
> Regards,
>
> Gary
>
> 


0
biffinpitt (3172)
5/3/2007 6:01:05 PM
Biff,

Your formulas worked perfect.

Thank you for the expertise and time,
Gary

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:e2NGH0ajHHA.4596@TK2MSFTNGP05.phx.gbl...
> If I understand .....
>
> ..........A..........B
> 1......Tom.......10
> 2......Bob...........
> 3......Joe.........10
> 4......Lou...........
> 5......Tom.......10
>
> You want Tom and Joe extracted and the total of their miles logged?
>
> Enter this array** formula in C1:
>
> =INDEX(A1:A5,MATCH(TRUE,B1:B5<>"",0))
>
> Enter this array** formula in C2:
>
> =INDEX(A1:A5,MATCH(1,(B1:B5<>"")*(A1:A5<>C1),0))
>
> Enter this formula D1 and copy to D2:
>
> =SUMIF(A$1:A$5,C1,B$1:B$5)
>
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER)
>
> Biff
>
> "Gary" <allge@cox.net> wrote in message 
> news:e$INPiajHHA.4772@TK2MSFTNGP05.phx.gbl...
>> In column "A" (A1:A20) titled "Employees", there's recorded employee 
>> names as work is done through a weeks time.  There are ten different 
>> names that are possible.
>>
>>
>>
>> In column "B" there are empty cells (B1:B20) titled "Miles".  When an 
>> employee logs miles on a day it is registered in column "B" adjacent to 
>> their name.  There are only two employees per sheet that will have 
>> recorded miles, although it is not always the same two.
>>
>>
>>
>> In cell "C1" & "C2" there are empty cells titled "Employee Miles".  We 
>> manually record the two employee names that have miles on that particular 
>> sheet in cells "C1" & "C2".
>>
>>
>>
>> In cell "D1" & "D2" there are empty cells titled "Miles Total".  We 
>> manually count the miles each of the two employees have recorded in 
>> different cells in column "B" through a weeks time and type the totals in 
>> cells "D1" & "D2".
>>
>>
>>
>> In the cells in column "C" we wish to have the names automatically appear 
>> based on if that particular name had any mileage recorded in column "B". 
>> In the cells in column "D" we wish to have the totals automatically 
>> appear based on the sum of miles in column "B" for that particular 
>> employee.
>>
>>
>>
>> How may we automate entries in "C1" & "C2" and "D1" & "D2"?
>>
>>
>>
>> Regards,
>>
>> Gary
>>
>>
>
> 


0
allge (63)
5/3/2007 10:29:13 PM
You're welcome. Thanks for the feedback!

Biff

"Gary" <allge@cox.net> wrote in message 
news:%23ejNGKdjHHA.4704@TK2MSFTNGP06.phx.gbl...
> Biff,
>
> Your formulas worked perfect.
>
> Thank you for the expertise and time,
> Gary
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:e2NGH0ajHHA.4596@TK2MSFTNGP05.phx.gbl...
>> If I understand .....
>>
>> ..........A..........B
>> 1......Tom.......10
>> 2......Bob...........
>> 3......Joe.........10
>> 4......Lou...........
>> 5......Tom.......10
>>
>> You want Tom and Joe extracted and the total of their miles logged?
>>
>> Enter this array** formula in C1:
>>
>> =INDEX(A1:A5,MATCH(TRUE,B1:B5<>"",0))
>>
>> Enter this array** formula in C2:
>>
>> =INDEX(A1:A5,MATCH(1,(B1:B5<>"")*(A1:A5<>C1),0))
>>
>> Enter this formula D1 and copy to D2:
>>
>> =SUMIF(A$1:A$5,C1,B$1:B$5)
>>
>> ** array formulas need to be entered using the key combination of 
>> CTRL,SHIFT,ENTER (not just ENTER)
>>
>> Biff
>>
>> "Gary" <allge@cox.net> wrote in message 
>> news:e$INPiajHHA.4772@TK2MSFTNGP05.phx.gbl...
>>> In column "A" (A1:A20) titled "Employees", there's recorded employee 
>>> names as work is done through a weeks time.  There are ten different 
>>> names that are possible.
>>>
>>>
>>>
>>> In column "B" there are empty cells (B1:B20) titled "Miles".  When an 
>>> employee logs miles on a day it is registered in column "B" adjacent to 
>>> their name.  There are only two employees per sheet that will have 
>>> recorded miles, although it is not always the same two.
>>>
>>>
>>>
>>> In cell "C1" & "C2" there are empty cells titled "Employee Miles".  We 
>>> manually record the two employee names that have miles on that 
>>> particular sheet in cells "C1" & "C2".
>>>
>>>
>>>
>>> In cell "D1" & "D2" there are empty cells titled "Miles Total".  We 
>>> manually count the miles each of the two employees have recorded in 
>>> different cells in column "B" through a weeks time and type the totals 
>>> in cells "D1" & "D2".
>>>
>>>
>>>
>>> In the cells in column "C" we wish to have the names automatically 
>>> appear based on if that particular name had any mileage recorded in 
>>> column "B". In the cells in column "D" we wish to have the totals 
>>> automatically appear based on the sum of miles in column "B" for that 
>>> particular employee.
>>>
>>>
>>>
>>> How may we automate entries in "C1" & "C2" and "D1" & "D2"?
>>>
>>>
>>>
>>> Regards,
>>>
>>> Gary
>>>
>>>
>>
>>
>
> 


0
biffinpitt (3172)
5/4/2007 1:52:33 AM
Reply:

Similar Artilces:

Expanding Text
Is there any way to have the text overprint more than one line? For example, Excel typed in cell c5 with an 18pt type will cut off the top half of all letters unless the row height is increased. Is it possible to have the text expand into C4 as opposed to increasing the height of the row? Paul Dulong It can extend into d5 if nothing else is there -- Don Guillett SalesAid Software donaldb@281.com "Paul Dulong" <pdulong@cogeco.ca> wrote in message news:imVLd.2555$Sx6.2...

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

How to write text to command prompt console windows?
I am using VC++ 6.0 to build a windows based application, however, my application allows user to input command arguments in the dos prompt windows. I want to have a warning message for user if they have input the wrong arguments. How can I write text to the dos prompt windows if user input the wrong arguments? Thanks in advance. Steven. Steven wrote: > I am using VC++ 6.0 to build a windows based application, however, my > application allows user to input command arguments in the dos prompt > windows. I want to have a warning message for user if they have input the > wrong ar...

Sum of a UNION Query
Hello, I want to sum the values together that are returned from the following two queries joined with a UNION from within the same query (instead of creating a second query that will SUM the two values that are produced from this one). I tried "wrapping" this code within a SELECT SUM, but it returns with "this is not allowing in subqueries". SELECT Count(*) AS SubCount FROM [TABLE1] WHERE (([TABLE1].USER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [TABLE2] WHERE (([TABLE2].USER)="EJONES"); The results are: SubCount -------------- 380 45...

conditional text merge
I want to merge text under the following criteria, First issue (data in Col A in sequence) Col A(text) Col B(text) a gg a hh a s b r b ee c tt d ss d ee Merge to: a gg hh s b r c tt d ss ee Second issue (data in Col A not in sequence) a gg b r a hh a s b ee d ss c tt d ee Merge to: a gg hh s b r c tt d ss ee Is it possible to merge it? TIA norik -- Message posted from http://www.ExcelForum.com Hi yes it is possible but will require some VBA. below one way: 1. Create a unique list for column A on a separate sheet: - selec...

Grey text prints as black..
I'm printing a page that has some text de-emphasized by using a grey font colour. Unfortunately when I print it comes out as black like the rest of the text. I looked for a setting in the Options and in the Print dialogs, but was unable to see anything that said "allow grayscale" anywhere. Suggestions anyone? thanks... R. I have the same problem and have not yet found a solution. could it be a configuration problem with the printer? Have you recently re-installed Excell? Julien "RGK" a écrit : > I'm printing a page that has some text de-empha...

conditional format formula
I am trying to use a formula in conditional format to turn a cell grey if the date in the column is not yet passed. not colored not colored should be grey a b c 10/20/04 10/21/04 10/22/04 5:00 5:00 5:00 =IF(NOW()<a$2,,) Is my formula. Whether I use < or > does not matter, th...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

Deleting Text
I know that with "&" I can add two text cells together. How do I get rid of text? In the example "Roddick, Andy (USA) " in A2, I want to get rid of "(USA) ". I can separate with "=right(A1,7)", the last seven characters, but how to "subtract" them from within A2" Felix In an un-used cell, enter: =LEFT(A2,LEN(A2)-7) then copy this cell and paste/special/value back onto A2 -- Gary''s Student - gsnu200827 "Felix" wrote: > I know that with "&" I can add two text cells together. > > How...

how to find a second comma in a text
Hi, I have a text as --> Madison, Dane, Wisconsin How can I find the position of the second comma? thanks, Boon Assuming there is *always* a 2nd comma... =FIND(",",A2,FIND(",",A2)+1) -- Biff Microsoft Excel MVP "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl... > Hi, > > I have a text as --> Madison, Dane, Wisconsin > > How can I find the position of the second comma? > > thanks, > Boon > > thanks. this works well fo...

How can I put text in a circle using Publisher ?
Have looked everywhere for an answer to this ? : How can I put text in a circle while using Publisher ? If I want to surround a shape or piece of clipart with words, I often need to have text in a circular or oval shape ........ surely this can be done with Publisher. Thanks for any expertise. DixieWins wrote: > Have looked everywhere for an answer to this ? : How > can I put text in a circle while using Publisher ? > > If I want to surround a shape or piece of clipart with > words, I often need to have text in a circular or oval > shape ........ surely this can be ...

How can I cross reference a SmartArt Graphic to a page/text?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to allow users to click on the SmartArt Graphic on my first page and automatically go to the respective page within the document. I click next to the wording on the graphic and go to Insert, but it will not allow me to insert the cross reference. You can do it the other way around - from the text to the graphic, but not from the graphic to the text. Anyone know a way around this or another way to do it? Objects cannot be used as active hyperlinks in a Word document. You can do what you describ...

Formula to eliminate zeros
I have a column of values that is 200 rows tall. The values are eithe a five digit option number or a zero. (I can change the formula tha generates this column to put anything in place of the zeros of tha helps) I need to transfer those option numbers to Work Order in concise list without 175 to 200 zeros. I need to do this automaticall with a formula when the data is imported so my purchasing dept. doe not have to filter the numerous lists with each job with a drop dow menu. I could have all of the zeros on the bottom if I can get all o the five digit option numbers to appear at the top...

Formula for file location
Hi (I'm a newbie so please be patient) I am using excel to build a database for an online store, i have apro 3000 products and each product needs an image path (eg. D:/m docs/images/123.gif). I need a formula that will automatically insert the path to the image in its cell in the image colomn. To make this easier all the image names are the the part number[.gif so the image for product 123 is D:/my docs/images.123.gif. Can any one offer a formula to do this? Code ------------------- Example of spread sheet -------------------------------------------------- Make Model ...

array formulas-sumproduct and average
Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

auto sum function
I just realized my final sum changes if I use the "sort ascending" function..... How do I fix this ? thank u Hi where is your sum formula located? -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > I just realized my final sum changes if I use the "sort > ascending" function..... > > How do I fix this ? > > thank u Down a colum But also, i just found out info changes on each lines when i use the "sort ascending" tool... >-----Original Message----- >Hi >where is your sum formula located? &...

Find and replace text thats not wdAlignParagraphCenter alignment
Im trying to make a macro that set alignment on all text to wdAlignParagraphJustify, except text that is wdAlignParagraphCenter. Right now i have this: With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphRight With .Replacement .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphJustify End With .Execute Replace:=wdReplaceAll End With With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphLeft ...

Need text in upper left corner of a cell
Hi Is it possible to have some text written in upper corner of a cell? I use Excel as a fill-in userform. I can justify the size of other columns, but it would be nice to write something into the cell, so the user understand what to fill inn. Any workarounds or other suggestions? Any help is appreciated. Gunnar Hi maybe you can use the comment functionality (goto 'Insert - Comment') to add some hints for the user -- Regards Frank Kabel Frankfurt, Germany Gunnar wrote: > Hi > > Is it possible to have some text written in upper corner > of a cell? > I use Excel a...

copying formula down
Using Excel 2003 Column A is dates. These dates plus 7 are being compared to the current date to determine whether "overdue" gets displayed in Column D. I'm using the following IF statement: If A1+7<today(),"overdue"," " When I copy the formula down, rows that have not yet had data entered into them are all displaying "overdue" because Column A is blank. Do I just need to remember to copy the formula down every time I enter a new row of data, or is there some way I can modify my IF statement to only do the calculation if there is data ...

Exporting As Text with X Number of Spaces Between Data
Is there a way to export data in a MS Excel spreadsheet as text and define the amount of spaces between the data being exported? The number of spaces need to vary. This concept is the opposite of importing text as "Fixed Width" and creating column break lines. Instead, I'd like to export columns into a text file and specify the amount of spaces between the text that is being exported from each column. The amount spaces in the text file needs to be different between each set of column data. Thanks! One way: Say you have 5 columns, and you want 2 spaces between A &...

"Disappearing" lines of text in Word docs
I've noticed a really unusual intermittent bug in my version of MS Word. Suppose there are two consecutive lines of text in my document, Line A and Line B. I want to insert a new line between Lines A & B, so I position the cursor in between them and hit return (not highlighting Line B beforehand, because I'm not trying to get rid of it). When I do so, Line B seems to disappear, with the cursor (and empty line in front of it) taking the place of Line B. However, when I hit "page up" or "page down", Line B reappears. Anyone know why this might ...

[ANN] New XML Text Editor in Visual Studio
Announcing: New XML Text Editor in Visual Studio 2005 Beta 1 Visual Studio 2005 Beta 1 contains a completely new XML Text Editor, built on top of the core text editor provided by Visual Studio. It is entirely written in C# and leverages all the cool stuff provided by the System.Xml ..NET assembly. The XML editor provides support for editing XML and DTD content, including special support for XSD and XSL and includes the following handy features: a.. Full syntax coloring for all XML and DTD syntax. b.. Well formedness checking while you type, with red squiggles and error list. c.. Intel...

Can I combine a Vlookup with an IF formula?
I'm not sure Excel can do what I want it to do - I have a VLookup formula on my spreadsheet which is returning data, however, I only want it displayed if it returns a specific condition. Is this possible ? -- CMB BT Hi, let' say you want to perform the vlookup if cell A1 is greater than 100, if not will leave the cell blank =if(A1>100,your formula,"") "Claire" wrote: > I'm not sure Excel can do what I want it to do - > > I have a VLookup formula on my spreadsheet which is returning data, however, > I only want it disp...

Why is "image and text" option grayed out for some buttons in the customization?
It so happens that the most frequently used buttons on the tool bar are the up-arrow and down-arrow (the ones that move to the next/previous item). As I tried to assign short-cut key to them, however, i found that the "image and text" option is grayed out - for that matter, all other display options are grayed out - when you go into customization mode. Why is "image and text" option unavailable for some buttons? ...