Extract

Month	Number
AUG	1
AUG	2
AUG	3
AUG	4
AUG	5
AUG	6
SEP	28
SEP	29
SEP	30
SEP	31
SEP	32
SEP	33
	
these r the data given in Sheet 1 and i want the highest number in the month 
of august in sheet 2
0
sajith (10)
9/22/2008 7:56:01 AM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
746 Views

Similar Articles

[PageSpeed] 39

=MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))

this is an array formula, so commit with Ctrl-Shift-Enter not just Enter

-- 
__________________________________
HTH

Bob

"sajith" <sajith@discussions.microsoft.com> wrote in message 
news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> Month Number
> AUG 1
> AUG 2
> AUG 3
> AUG 4
> AUG 5
> AUG 6
> SEP 28
> SEP 29
> SEP 30
> SEP 31
> SEP 32
> SEP 33
>
> these r the data given in Sheet 1 and i want the highest number in the 
> month
> of august in sheet 2 


0
BobNGs (423)
9/22/2008 9:12:38 AM
i got the result as 33 but its not true, i want the highest number only for 
the period of AUG

"Bob Phillips" wrote:

> =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> 
> this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "sajith" <sajith@discussions.microsoft.com> wrote in message 
> news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > Month Number
> > AUG 1
> > AUG 2
> > AUG 3
> > AUG 4
> > AUG 5
> > AUG 6
> > SEP 28
> > SEP 29
> > SEP 30
> > SEP 31
> > SEP 32
> > SEP 33
> >
> > these r the data given in Sheet 1 and i want the highest number in the 
> > month
> > of august in sheet 2 
> 
> 
> 
0
sajith (10)
9/22/2008 11:41:01 AM
Bob's formula worked fine for me.

Are you sure you didn't miss the Aug with 33?  Maybe in a hidden/filtered row?

sajith wrote:
> 
> i got the result as 33 but its not true, i want the highest number only for
> the period of AUG
> 
> "Bob Phillips" wrote:
> 
> > =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> >
> > this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "sajith" <sajith@discussions.microsoft.com> wrote in message
> > news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > > Month Number
> > > AUG 1
> > > AUG 2
> > > AUG 3
> > > AUG 4
> > > AUG 5
> > > AUG 6
> > > SEP 28
> > > SEP 29
> > > SEP 30
> > > SEP 31
> > > SEP 32
> > > SEP 33
> > >
> > > these r the data given in Sheet 1 and i want the highest number in the
> > > month
> > > of august in sheet 2
> >
> >
> >

-- 

Dave Peterson
0
petersod (12004)
9/22/2008 12:03:56 PM
i am getting the result as 33, but the highest number for the month of AUG is 
6. the highest number for the month of SEP is 33. i want to get the highest 
number for the month of AUG on sheet 2.

"Dave Peterson" wrote:

> Bob's formula worked fine for me.
> 
> Are you sure you didn't miss the Aug with 33?  Maybe in a hidden/filtered row?
> 
> sajith wrote:
> > 
> > i got the result as 33 but its not true, i want the highest number only for
> > the period of AUG
> > 
> > "Bob Phillips" wrote:
> > 
> > > =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> > >
> > > this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> > >
> > > --
> > > __________________________________
> > > HTH
> > >
> > > Bob
> > >
> > > "sajith" <sajith@discussions.microsoft.com> wrote in message
> > > news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > > > Month Number
> > > > AUG 1
> > > > AUG 2
> > > > AUG 3
> > > > AUG 4
> > > > AUG 5
> > > > AUG 6
> > > > SEP 28
> > > > SEP 29
> > > > SEP 30
> > > > SEP 31
> > > > SEP 32
> > > > SEP 33
> > > >
> > > > these r the data given in Sheet 1 and i want the highest number in the
> > > > month
> > > > of august in sheet 2
> > >
> > >
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
sajith (10)
9/22/2008 2:36:17 PM
Share your formula.

sajith wrote:
> 
> i am getting the result as 33, but the highest number for the month of AUG is
> 6. the highest number for the month of SEP is 33. i want to get the highest
> number for the month of AUG on sheet 2.
> 
> "Dave Peterson" wrote:
> 
> > Bob's formula worked fine for me.
> >
> > Are you sure you didn't miss the Aug with 33?  Maybe in a hidden/filtered row?
> >
> > sajith wrote:
> > >
> > > i got the result as 33 but its not true, i want the highest number only for
> > > the period of AUG
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> > > >
> > > > this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> > > >
> > > > --
> > > > __________________________________
> > > > HTH
> > > >
> > > > Bob
> > > >
> > > > "sajith" <sajith@discussions.microsoft.com> wrote in message
> > > > news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > > > > Month Number
> > > > > AUG 1
> > > > > AUG 2
> > > > > AUG 3
> > > > > AUG 4
> > > > > AUG 5
> > > > > AUG 6
> > > > > SEP 28
> > > > > SEP 29
> > > > > SEP 30
> > > > > SEP 31
> > > > > SEP 32
> > > > > SEP 33
> > > > >
> > > > > these r the data given in Sheet 1 and i want the highest number in the
> > > > > month
> > > > > of august in sheet 2
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
9/22/2008 4:34:57 PM
i was using bobs formula =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200)) 

"Dave Peterson" wrote:

> Share your formula.
> 
> sajith wrote:
> > 
> > i am getting the result as 33, but the highest number for the month of AUG is
> > 6. the highest number for the month of SEP is 33. i want to get the highest
> > number for the month of AUG on sheet 2.
> > 
> > "Dave Peterson" wrote:
> > 
> > > Bob's formula worked fine for me.
> > >
> > > Are you sure you didn't miss the Aug with 33?  Maybe in a hidden/filtered row?
> > >
> > > sajith wrote:
> > > >
> > > > i got the result as 33 but its not true, i want the highest number only for
> > > > the period of AUG
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> > > > >
> > > > > this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> > > > >
> > > > > --
> > > > > __________________________________
> > > > > HTH
> > > > >
> > > > > Bob
> > > > >
> > > > > "sajith" <sajith@discussions.microsoft.com> wrote in message
> > > > > news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > > > > > Month Number
> > > > > > AUG 1
> > > > > > AUG 2
> > > > > > AUG 3
> > > > > > AUG 4
> > > > > > AUG 5
> > > > > > AUG 6
> > > > > > SEP 28
> > > > > > SEP 29
> > > > > > SEP 30
> > > > > > SEP 31
> > > > > > SEP 32
> > > > > > SEP 33
> > > > > >
> > > > > > these r the data given in Sheet 1 and i want the highest number in the
> > > > > > month
> > > > > > of august in sheet 2
> > > > >
> > > > >
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
sajith (10)
9/23/2008 3:54:10 AM
I would double check the data.

I think you have that value for Aug.

sajith wrote:
> 
> i was using bobs formula =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> 
> "Dave Peterson" wrote:
> 
> > Share your formula.
> >
> > sajith wrote:
> > >
> > > i am getting the result as 33, but the highest number for the month of AUG is
> > > 6. the highest number for the month of SEP is 33. i want to get the highest
> > > number for the month of AUG on sheet 2.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Bob's formula worked fine for me.
> > > >
> > > > Are you sure you didn't miss the Aug with 33?  Maybe in a hidden/filtered row?
> > > >
> > > > sajith wrote:
> > > > >
> > > > > i got the result as 33 but its not true, i want the highest number only for
> > > > > the period of AUG
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200))
> > > > > >
> > > > > > this is an array formula, so commit with Ctrl-Shift-Enter not just Enter
> > > > > >
> > > > > > --
> > > > > > __________________________________
> > > > > > HTH
> > > > > >
> > > > > > Bob
> > > > > >
> > > > > > "sajith" <sajith@discussions.microsoft.com> wrote in message
> > > > > > news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com...
> > > > > > > Month Number
> > > > > > > AUG 1
> > > > > > > AUG 2
> > > > > > > AUG 3
> > > > > > > AUG 4
> > > > > > > AUG 5
> > > > > > > AUG 6
> > > > > > > SEP 28
> > > > > > > SEP 29
> > > > > > > SEP 30
> > > > > > > SEP 31
> > > > > > > SEP 32
> > > > > > > SEP 33
> > > > > > >
> > > > > > > these r the data given in Sheet 1 and i want the highest number in the
> > > > > > > month
> > > > > > > of august in sheet 2
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
9/23/2008 12:53:30 PM
Reply:

Similar Artilces:

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

MID formula not extracting what I want.
I'm trying to extract the date from a cell using the MID formula and all I'm getting is some weird number that does not seem to make sense. I have tried formatting the target cell but still no change. Any clues about it? How can I do this? 1/13/2006 12:16:29 AM =MID(E5,1,9) 38730.011 -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860 View this thread: http://www.excelforum.com/showthread.php?threadid=501062 Hi That's because Excel stores dates as...

Extracting data to make mailing list
My neighbour has a .PUBS name and address doc. The doc is 10 pages long. Each page of the document is a table with 10 rows and 3 columns. Each cell is name on top the line followed by 2 or 3 lines of address. She then just cuts the full sheet Avery into mailing labels. This has become too difficult to manage, sort, filter, etc. Anyone have a suggestion how to extract this data so I can make a Publisher mailing list? Thanks. -- Ross try bringing it into Excel...you'll some "massaging" to do. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Ross"...

Extract
Month Number AUG 1 AUG 2 AUG 3 AUG 4 AUG 5 AUG 6 SEP 28 SEP 29 SEP 30 SEP 31 SEP 32 SEP 33 these r the data given in Sheet 1 and i want the highest number in the month of august in sheet 2 =MAX(IF(Sheet1!A2:A200="AUG",Sheet1!B2:B200)) this is an array formula, so commit with Ctrl-Shift-Enter not just Enter -- __________________________________ HTH Bob "sajith" <sajith@discussions.microsoft.com> wrote in message news:297BFDD5-156E-46DC-9408-D05F81F6A2C6@microsoft.com... > Month Number > AUG 1 > AUG 2 > AUG 3 > AUG 4 > AUG 5 > AUG 6 > S...

Extract text content from MS office files
Hi, I want to extract text content MS-OFFICE documents like word, ppt, xls... Plz gimme some tips. Regard Jack ...

Extracting First Letter
Can anyone help, i'm trying to extract the first letter of each word within any given phrase. I'm using excel 2003 -- Thanks Perhaps this might suffice With text running in A1 down In B1: =LEFT(TRIM(A1),1) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "crackle" wrote: > Can anyone help, i'm trying to extract the first letter of each word within > any given phrase. I'm using excel 2003 > -- > Thanks Sorry Max, let me be more specific, eg. Lets say I have the phrase "The quick brown fox jumped over the moon&qu...

Extracting folder data / Outlook won't open
I can't open Outlook, because it doesn't recognize Outlook Express is installed (I have the often reported, but apparently rarely solved, msoe.dll problem). Is there a way to pull the mountains of data (calendar, tasks, contacts and saved messages) out of my Outlook data file from another PIM program? I'm willing to go with any other program to get away from buggy Outlook, and figure one of the third-party sync programs will handle the issue of how to sync with my Pocket PC. augusthelmbright@msn.com, you wrote on 23 Mar 2007 14:57:55 -0700: > I can't open Outlook, beca...

String extraction
Hi How can I extract the first line form a multi-line string? Many Thanks Regards hi John, John wrote: > How can I extract the first line form a multi-line string? Use Left(yourString, InStr(yourString, Chr(13) & Chr(10))) to get the first line. mfG --> stefan <-- "Stefan Hoffmann" <stefan.hoffmann@explido.de> wrote in message news:uDOmMrJ6HHA.1208@TK2MSFTNGP03.phx.gbl... > hi John, > > John wrote: >> How can I extract the first line form a multi-line string? > Use Left(yourString, InStr(yourString, Chr(13) & Chr(10))) to get the ...

extracting unique #'s from multiple columns
I have some data in two columns and i want to extract the uniqu records, I would use a combination of the if and countif if I wer only dealing with 1 column but with two columns I am stumped. EX order# box# 123 1 123 1 123 2 123 3 125 65 125 65 i NEED TO BE ABLE TO SEPERATE AND COUNT THE UNIQUE RECORDS 123 1 123 2 123 3 125 6 -- Message posted from http://www.ExcelForum.com Hi one way: - create a helper column C - in C1 enter the formula: =A1 & "^" & B1 - copy this down and use your approah ...

Programming to extract automatically extract attachments
Hi, Can anyone teach me how to write a code to automatically extract attachement from incoming email sorted out into a pre-assigned folder. (Note: The incoming email always has a fix caption and it already sorted into a pre-assigned folder) Background:- The emails are send from my surveillance camera when it detect a motion Thank you. Nelson Chou This example demonstrates how to extract an attachment, skip the printing part. If your folder is not the default inbox but a subfolder of it, add this in Applicaton_Startup before setting the Items variable: Set Folder...

Extracting Data
Hi really hope someone can help I have been sent a Word documents 250 pages long and which contains addresses. I need to get these into either Excel or Access. Each address is 4 lines and there is 2 or 3 line feeds between each address. Is there anyway I can extra this data into and Excel worksheet or Access table? Very many thanks Alex On Sun, 28 Mar 2010 16:50:41 +0100, Alex Hammerstein <aph@misnet.co.uk> wrote: >Hi really hope someone can help > >I have been sent a Word documents 250 pages long and which contains >addresses. I need to get thes...

preventing resources from being extracted in a win32 executable
Does anybody know how to prevent the resources (icons, bitmaps) from being extracted from a win32 executable? Thanks; Dom <dominicgualtieri@hotmail.com> wrote in message news:1193596073.526314.221210@v3g2000hsg.googlegroups.com... > Does anybody know how to prevent the resources (icons, bitmaps) from > being extracted from a win32 executable? > The only way is to encrypt the resources, perhaps storing them in an external file. -- David You can encrypt them, as suggested, but the decrypt code can be reverse-engineered and thus easily cracked. It also means you can'...

Extracting Data #4
I will like to create a formula that will extract dava from a column of text that has quatation. So in the column any text that has "" in the word extract it. Any ideas? I tried everything. Thanks in advance! Hi Dave not quite sure about your data. could you give an example? -- Regards Frank Kabel Frankfurt, Germany "Dave" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:1db501c42723$e59e9350$a501280a@phx.gbl... > I will like to create a formula that will extract dava > from a column of text that has quatation. So in the > column an...

Extract Unique Values, Then Extract Again to Remove Suffixes
I have a list of builders where I want to extract all the unique values. Using =IF(COUNTIF($A$1:A1,A1)=1,A!,"") works fine to get the first set of unique values, but I need to extract it a bit further. The builder names may be Ryan 50, Ryan 60 or just Ryan. I need to combine those values to come up with the final list of unique builders that just says Ryan. I'm not sure if I can strip off anything from the end as some are Ryan - Greenbrier. I thought maybe removing everything to the right of a blank space, but those entries have 2 blanks. Any help would be greatly appr...

Extracting from 1 cell and placing extractions in multiple cells
I receive the following data in a (3) cells: SYS DESKTOP PIII 500 6.4 G 128M 40X 1.44(1 cell) DELL(1 cell) OPTIPLEX GX1(1 cell) Is there any code or command that would extract and put in multiple cells: Dell Optiplex GX1 (one cell) DESKTOP (1 cell) PIII500 (one cell) 128MB (one cell) 6.4 G (one cell) 40X (one cell) The RAM (128MB) can be anywhere from 32MB-512MB The HDD (6.4 G) can be from 1.2-20.4 G I receive in 100 computers from a company and it takes me an hour (at least) to type in this stuff manually. Your help would be appreciated. Thank you. Larry -- lkw441 ---------------...

Extract dd and mm from text and put that plus current yy in another
I've got a workbook which comes from someone else and it's got a cell in it from which I want to get the above. The format of the cell varies. It could be texttexttext 17Feb09, texttexttext 17th Feb, texttexttext 17 February 09, texttexttext 17th February 2009, etc (but never Feb 17 - I'm in UK not US!) Try Text to Columns (in the Data menu). It should convert most every date for you. Regards, Fred "robzrob" <robzrob@hotmail.com> wrote in message news:e3148371-eef1-4e36-a3e6-7ab53c3ce5f1@g11g2000yqe.googlegroups.com... > I've got a workboo...