Formula help please

HI, HELP REQUIRED WITH FORMULA FOR EXCEL PLEASE

Q:

EXCEL WORKBOOK WITH TWO SHEETS, ONE TITLED MON & THE OTHER STAT.
ON SHEET MON, I HAVE TWO COLUMNS (H, K) WITH A RANGE OF SAY 20 LINES EACH.

ON SHEET STAT, I HAVE TWO CELLS (B5, F5) ON A LINE.

I WANT ‘MON’ ‘H’ TO COMPARE WITH ‘STAT’ ‘B5’.
ON ‘H’ LINES THAT MATCH WITH ‘B5’, IF A NUMBER  IS ENTERED IN THE 
CORRESPONDING ‘MON’ ‘K’ LINE,  ‘STAT’ ‘F5’ WILL COUNT IT.

EXAMPLE:
‘STAT’ B5 = MM
‘MON’ H8 & H12 = MM
‘MON’ K8 = 0800 & K12=’ANYTHING BUT A NUMBER’
RESULT ON ‘STAT’ F5 SHOULD BE ‘1’

THANKS!
JOHN

0
Utf
5/6/2010 10:31:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
896 Views

Similar Articles

[PageSpeed] 22

Hi John,

Please don't SHOUT, you'll give us a headache.

=SUMPRODUCT((Mon!$A$1:$A$20=Stat!$B5)*(ISNUMBER(Mon!$K$1:$K$20)))

should work, but I'm a little bit worried that you wrote 0800 rather than 
800.  Is that a text entry?  You might need:

=SUMPRODUCT((Mon!$A$1:$A$20=Stat!$B5)*
(ISNUMBER(VALUE(Mon!$K$1:$K$20))))

and if 0800 is the start of a telephone number, e.g. "0800 1234567"  you 
will need:

=SUMPRODUCT((Mon!$A$1:$A$20=Stat!$B5)*
(ISNUMBER(VALUE(SUBSTITUTE(Mon!$K$1:$K$20," ","")))))


HTH
Steve D.


"John" <John@discussions.microsoft.com> wrote in message 
news:411CE718-1807-4D4B-9A50-C28493436A84@microsoft.com...
> HI, HELP REQUIRED WITH FORMULA FOR EXCEL PLEASE
>
> Q:
>
> EXCEL WORKBOOK WITH TWO SHEETS, ONE TITLED MON & THE OTHER STAT.
> ON SHEET MON, I HAVE TWO COLUMNS (H, K) WITH A RANGE OF SAY 20 LINES EACH.
>
> ON SHEET STAT, I HAVE TWO CELLS (B5, F5) ON A LINE.
>
> I WANT ‘MON’ ‘H’ TO COMPARE WITH ‘STAT’ ‘B5’.
> ON ‘H’ LINES THAT MATCH WITH ‘B5’, IF A NUMBER  IS ENTERED IN THE
> CORRESPONDING ‘MON’ ‘K’ LINE,  ‘STAT’ ‘F5’ WILL COUNT IT.
>
> EXAMPLE:
> ‘STAT’ B5 = MM
> ‘MON’ H8 & H12 = MM
> ‘MON’ K8 = 0800 & K12=’ANYTHING BUT A NUMBER’
> RESULT ON ‘STAT’ F5 SHOULD BE ‘1’
>
> THANKS!
> JOHN
> 

0
Steve
5/6/2010 10:48:47 AM
Reply:

Similar Artilces:

help again #2
Looking for a formula where cell E1 will add cell A1 to the lowest total from cell b1 c1 or d1, then the same with the formula but this time with the highest value. Thanks again to all who reply. One way: E1: =A1+MIN(B1:D1) F1: =A1+MAX(B1:D1) In article <Ctkfj.980$EA5.564@pd7urf2no>, "Bob Bob" <NOeMAILl@mail.com> wrote: > Looking for a formula where cell E1 will add cell A1 to the lowest total > from cell b1 c1 or d1, then the same with the formula but this time with > the highest value. > Thanks again to all who reply. Thanks it did the j...

retrieving mail from another location on other computer. HELP
When i travel i don't always go with my own laptop. Can i access my outlook mail on the web ? If your mail provider makes it available on the web, yes. "Juliemrm" <Juliemrm@discussions.microsoft.com> wrote in message news:D0191906-4B63-4020-AAC4-58041BD51703@microsoft.com... : When i travel i don't always go with my own laptop. Can i access my outlook : mail on the web ? Yes & No You wont be able to access the mail you have downloaded / sent within Outlook, unless its an imap account Otherwise see Tom's post "Juliemrm" <J...

Help please - tried everything - nothing works
Hi I look after a network for a charity in the UK. We recently implemented web content filtering using Webmarshal from netiq. Users now access the internet using webmarshal's inbuilt proxy server. Since introducing this, users of MS Publisher can no longer access online clipart from within the Publisher programme - by doing a search. You can however view online content by browsing categories in clip organiser. I've been assuming that the issue is with the proxy server configuration and have added various microsoft.com addresses (the ones I've found reference to in Newsgroups) ...

Graph Help- 12 month graph, but only want months that have passed
I need help setting up a graph. It is a simple bar graph, that has the months of the year on the x axis, and production in tons on the y axis. I have a table setup for this Jan to Dec, but obviously only have data for Jan through May. Is there a way I can set up the graph so it will only make the graph Jan through May, but as I add more data, and months go by, it will automatically add a new column for June, then July and so on and so on? I do not want the months that have no data to be graphed with a value of zero, I would rather them show up automatically when that month has data....

help help help IF function
i am new to excell and need help with a problem...... the problem is iam looking for a amount < = 3000.00 and iam to display in bold and in light green i have never had to deal with that i got as far as =IF<=3000 then iam lost or it that even right?? I'm far from a whiz at Excel but I'd use conditional formatting. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "kimmy" <kimmy@discussions.microsoft.com> wrote in message news:ADC77884-FC87-47A3-90F2-D2AAF957F5D1@microsoft.com... >i a...

Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...

Help is needed with Conditional Formatting drudgery
I am trying desperately to reduce mouse clicks (and carpal tunnel syndrome risk!) with the following problem: I have 90 SETS of three price calculations, each trio equipped with its own drop down list which determines whether certain cells should be blacked out. The calculations are distributed among 30 tabs, an average of 3 sets of calculations per each tab. I have been copying the blank forms over the different tabs and I have been manually adjusting (a) the cell links for each drop down list and (b) all the conditional format formulas (I have been using the format painter to as m...

2004 Word, Entourage, Excel "Help" text selections: how copy to Notes?
How does one highlight and then copy text from Help components of Office 2004's Word, Excel and Entourage? "Select all" and "copy" are grayed out and command-c and command-a do not work with "Help" text in any of the Office 2004 components. If there were some way to copy and paste these selections into Entourage Notes, one could do boolean searches for multiple criteria within "Help" notes. This also would allow one to add his or her notations to the "Help" passages copied into Notes. It would also make it possible to put the user&#...

Need Help on Ranking
I am looking for a formula to rank the position of a person based o tasks completed. Conditions: The total number of tasks is the same for all (18) More then one person can have the same amount of tasks done placin them in the same ranking. Ranking Must be in a linier progression without skipping ANY number (RANK 1,2,3,4,5 etc�) The end result will need to look something like this TOTAL TASKS: 18 NAME----TASKS DONE---------RANK John------------4--------------------3 Bill---------------2-------------------5 Karen-----------4-------------------3 Jim---------------3-------------------4 Sal...

Freezing the cell (with Random formula)
Everytime I copy the cell (wth rand() formula) and paste it to another cell (use paste special - value - ok), the value of the original cell changes. How can I freeze the value of the original cell? Thanks a lot !! SEE! I'm not the only one! :) I have been trying to figure that one out for weeks. "Christy" <Christy@discussions.microsoft.com> wrote in message news:43B71A83-248D-4715-B751-8A0BCF7F10B8@microsoft.com... > Everytime I copy the cell (wth rand() formula) and paste it to another > cell > (use paste special - value - ok), the value of the origina...

How To Customise Autofill for a Formula
I don't know if this is possible but what i'm looking to do is customis the way autofill works when copying formulas. I currently have a formula which references cells in other workbooks t get an average: =AVERAGE('[2006-02-03.xls]Point 2 B0'!$B$37,'[2006_02_10.xls]Point B0'!$B$37,'[2006_02_17.xls]P2 B0'!$B$37) All the sheets have the same layout so it's always the same Column an row but when I drag down to autofill, it copies without changing th row number. So for the example above all the formulas reference ro 37. Is there a way I can get excel to se...

HeLP!! #2
I need helping using Excel to make a process faster- maybe there' another way around this?!?! My father owns a Towing company, and h puts the abandoned/wrecked cars he tows in, into an excel file, as h tows them in. Well there is a lot of court paperwork involved i actually getting the vehicle to the point where we can auction it off Is there a way using excel to have it print out reports for each row i a table using each cell in a different place in the report? Withou using a program such as crystal reports -- Message posted from http://www.ExcelForum.com Depending on what your documen...

Still need help
I have a spreadsheet that is 3604 rows & 6 columns. Column E has only hyperlinks to URL's. I have filtered my data via column B. I tried to A-Z Column A. When I do, the hyperlinks do not stay with the data. The mask looks correct but when you scroll over the hyperlink you see a completely different URL.????? Can anyone tell me how to make sure the Hyperlink keeps the correct URL when filtering and sorting the data. "Tim Williams" wrote: > Try using the =HYPERLINK() worksheet function Thanks Tim for answering, hoping you're still there. I tried it and i...

help 04-08-10
I am so lost on this thing Do you have an existing email address? If so, what is the part of your email address after the @ symbol? That part represents your mail service provider. Your mail provider will typically have a web page that shows how to=20 set up an email program. If you need more help, tell us the name of your email service provider. If you currently don't have an email=20 address, we can help with that also. I see you posted with a live.com email address. Is that your real email address? Never post your real email address in a public place, unless you like to r...

Could someone help a beginner to CComSafeArray?
Hi, I am writing a client program to enumerate the edges for a face. I want to use the CComSafeArray but I am not sure the following code has any potential error. Could someone kindly investigate it? CComSafeArray<VARIANT> edges; CComVariant v = face.GetEdges(); // return a variant of a safearray of IDispatch* edges.Attach(v.parray); v.parray = 0; // to avoid v's destructor to release the memory; let edges's desctuctor do it int nCount = edges.GetCount(); for (int i=0; i<nCount; i++) { IDispatch *p = *edges[i].ppdispVal; ... JD ...

Need help printing a single 4x6 postcard in Pub 2007
I set up my printer with the custom size. Then I created a new document in Publisher with the sheet size and page size as 4x6 (6" width consistent in all set ups). When I create my document, the ruler shows 4x6, but when I go to print, it's set up on a 8.5 x 11 page. I also tried setting up a document with multiple pages to sheet using a 6" wide sheet 2 up, but the print preview shows printing in the middle of the page. I've looked at all the help and forum questions I can find, and haven't been able to solve this problem. Any suggestions? Thanks. Which vers...

Need Help with configuring 70 profiles
Hello Everybody, Our company decided to use Outlook XP rather than squeral webmail. I have 70 workstations with Windows XP and Office XP and every user is a local administrator for his machine. is there any login script that will allow me to configure outlook XP with Internet Mail without any user input. Any help or direction is very much appreciated. Thank you in advance. Adam I believe the custom install wizard (part of the office resource kit) will allow you to make an outlook template (*.prf) that can add POP/SMTP accounts. You can then take this template and write some sort of scri...

Help with Excel sheets
Hello. I need help with developing a formula for data extraction: Spreadsheet #1 contains student data which my IT dept. imports into a student intake database. Prior to import, however, I would like to extract test score information from a second spreadsheet. The common identifiers between the two spreadsheets are "Last Name" and "First Name" to identify the students. The columns I want to extract and place at the end of each student's data from sheet #2 to sheet #1 are "Total", "Written" and "Reading". I know this ...

Exchange is driving me crazy please help.
I have just started working at a company last weeek appearently they have lot of issues with Exchange. We have lost our email and our clusters went down for two hours. After sometime we figured out the service accounts for exchange and cluster was locked out for some reason. After unlocking the accounts manually. Cluster and the exchange came back online. Now internal users are unable to email to internal users on the other servers. Example server A can not send email to server B. We have three exchange servers in our enviroment and they are clustered. They can send emails to the inter...

Help
I recently had to reinstall Windows (XP) to fix some corrupt files. Everything works fine now. I went to open Money this morning and immediately got a message telling me that it couldn't find the file MyMoney.mny. I located it (it was in it's normal place) and opened it but Money isn't finding any infomation in it (it's over 2mb) I do have a back up on a floppy but it's over 2 weeks old and would rather not attempt to use it. Can anyone tell me why I can't access the data in the MyMoney.mny file or it's associated disk backup? Thanks, I found a back up file in C...

Help and Support Services
Greetings, Here is the issue when clicking on a link in an event it opens Help and Support but it goes to a BING search page and then tells me "Were are sorry the requested page cannot be found". How can I reset the page to redirect to the correct Help and Support Page? =?Utf-8?B?SmFtZXMgUm9iZXJ0c29u?= <JamesRobertson@discussions.microsoft.com> wrote in news:4C3A040E-83DC-40AC-B83A-A632214C5D63@microsoft.com: > Greetings, Here is the issue when clicking on a link in an event it > opens Help and Support but it goes to a BING search page and then > tel...

I could really need your help on importing contacts into Outlook (Please?)
Hi all, I need to import literally 3000 contacts into Outlook. I need to do this for a survey I am conducting for my academic thesis (so, this is not a business problem, and I don't have much money to spend :-(. I am going to mail these companies and am going to send them a questionairre; therefore I need to have these records in Outlook. I want to do this via Excel (since importing it directly into Outlook is not possible due to the list format). Unfortunately, the list (in plain text) looks like the list on the bottom of my message (small example). (The list is Excel is in 2 column...

formula #5
I am working with a worksheet. As best I can explain: Cell b3 has a value of 4 If Cell b4=b3 than B4 If Cell b4<b3 than b4 I want to add that if: cell b5=b4 than b4 cell b5 <B4 than b5 The Only thing I presently have written is: IF(B4=B3,B4,IF(B4<B3,B4)) George, Try something like this- =IF(OR(B4<=B3,B5=B4),B4,IF(B5<B4,B5,"Unknown")) You'll have to decide what you want to happen, instead of "Unknown"! regards JohnI "George Yorks" <gyorks@comcast.net> wrote in message news:7b2301c37e51$d2c06220$a601280a@phx.gbl... > I...

help with calculating overtime in a time sheet
Hi Guys, I'm sure the question has been asked from time to time, but this on has got me a bit stumped, and looking at other peoples questions, jus confuses me even more! so basically I just wanna be able to enter times say from 0600 am till 1953 with a .5 lunch break Basically what I want from this, is a cell for normal hours up to 10, cell for overtime hours after 10 and a total. i've managed to get a total, and overtime hours, but not normal hours! Then for the totals I want a cell that works out normal hours (which should always be 7 anyway) another cell that works out time an...

Help recovering MS Word DOCX files
I had three MS Word 2007 docx files on my hard drive. I deleted them and emptied the trash. I immediately used some File Recovery programs to try to recover them. The programs were unable to find them. That seems very strange to me. Is it possibly because they are docx files? This is a relatively new file type by Microsoft, right? I am using MS Word with Vista Home Premium. I should probably add that the programs cannot find files with the names of the files I deleted. Are the names changed when they are deleted and the Recycle Bin emptied? I have heard that files are not wiped c...