If Question 02-03-10

Hi everyone

Hope this isn't a silly question (as think I may have made it harder in my 
head than it needs to be! given all the formulas I'm putting in are bringing 
back errors!!)

I have one worksheet which gathers info from other sheets and has the 
following info

A         B       C       D
Name   %      %      %

In another worksheet I have a list of names (column M say M3) next to the 
names I want to put in a formula that will give me the following results:

If M3 is in worksheet 1 and the corresponding cells to that name in b, c and 
d are all >0% write Yes otherwise write No but if M3 doesnt match anything on 
the list write - .

Thanks as always

Kiwi
0
Utf
2/3/2010 3:14:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
773 Views

Similar Articles

[PageSpeed] 21

Try this...

Assuming this data:

> A         B       C       D
> Name   %      %      %

Is on Sheet1 in the range A1:D10

=IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(Sheet1!B1:D10,MATCH(M3,Sheet1!A1:A10,0),0),">0")=3,"Yes","No"),"-")

-- 
Biff
Microsoft Excel MVP


"Kiwi" <Kiwi@discussions.microsoft.com> wrote in message 
news:89BF9F4D-AD44-427C-B46A-19DDED7B8D0A@microsoft.com...
> Hi everyone
>
> Hope this isn't a silly question (as think I may have made it harder in my
> head than it needs to be! given all the formulas I'm putting in are 
> bringing
> back errors!!)
>
> I have one worksheet which gathers info from other sheets and has the
> following info
>
> A         B       C       D
> Name   %      %      %
>
> In another worksheet I have a list of names (column M say M3) next to the
> names I want to put in a formula that will give me the following results:
>
> If M3 is in worksheet 1 and the corresponding cells to that name in b, c 
> and
> d are all >0% write Yes otherwise write No but if M3 doesnt match anything 
> on
> the list write - .
>
> Thanks as always
>
> Kiwi 


0
T
2/3/2010 4:07:56 AM
Try the below formula...

=IF(COUNTIF(Sheet1!A1:A100,M3),IF(SUMPRODUCT((Sheet1!A1:A100=M3)*
(Sheet1!B1:D100=0)),"No","Yes"),"-")

-- 
Jacob


"Kiwi" wrote:

> Hi everyone
> 
> Hope this isn't a silly question (as think I may have made it harder in my 
> head than it needs to be! given all the formulas I'm putting in are bringing 
> back errors!!)
> 
> I have one worksheet which gathers info from other sheets and has the 
> following info
> 
> A         B       C       D
> Name   %      %      %
> 
> In another worksheet I have a list of names (column M say M3) next to the 
> names I want to put in a formula that will give me the following results:
> 
> If M3 is in worksheet 1 and the corresponding cells to that name in b, c and 
> d are all >0% write Yes otherwise write No but if M3 doesnt match anything on 
> the list write - .
> 
> Thanks as always
> 
> Kiwi
0
Utf
2/3/2010 4:35:01 AM
Thank you both so much - both very helpful :-)
-- 
Thanks as always

Kiwi


"T. Valko" wrote:

> Try this...
> 
> Assuming this data:
> 
> > A         B       C       D
> > Name   %      %      %
> 
> Is on Sheet1 in the range A1:D10
> 
> =IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(Sheet1!B1:D10,MATCH(M3,Sheet1!A1:A10,0),0),">0")=3,"Yes","No"),"-")
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Kiwi" <Kiwi@discussions.microsoft.com> wrote in message 
> news:89BF9F4D-AD44-427C-B46A-19DDED7B8D0A@microsoft.com...
> > Hi everyone
> >
> > Hope this isn't a silly question (as think I may have made it harder in my
> > head than it needs to be! given all the formulas I'm putting in are 
> > bringing
> > back errors!!)
> >
> > I have one worksheet which gathers info from other sheets and has the
> > following info
> >
> > A         B       C       D
> > Name   %      %      %
> >
> > In another worksheet I have a list of names (column M say M3) next to the
> > names I want to put in a formula that will give me the following results:
> >
> > If M3 is in worksheet 1 and the corresponding cells to that name in b, c 
> > and
> > d are all >0% write Yes otherwise write No but if M3 doesnt match anything 
> > on
> > the list write - .
> >
> > Thanks as always
> >
> > Kiwi 
> 
> 
> .
> 
0
Utf
2/3/2010 10:57:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Kiwi" <Kiwi@discussions.microsoft.com> wrote in message 
news:2C1DE1C3-51F6-49F9-9BC4-66C69EDCC5DE@microsoft.com...
> Thank you both so much - both very helpful :-)
> -- 
> Thanks as always
>
> Kiwi
>
>
> "T. Valko" wrote:
>
>> Try this...
>>
>> Assuming this data:
>>
>> > A         B       C       D
>> > Name   %      %      %
>>
>> Is on Sheet1 in the range A1:D10
>>
>> =IF(COUNTIF(Sheet1!A1:A10,M3),IF(COUNTIF(INDEX(Sheet1!B1:D10,MATCH(M3,Sheet1!A1:A10,0),0),">0")=3,"Yes","No"),"-")
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Kiwi" <Kiwi@discussions.microsoft.com> wrote in message
>> news:89BF9F4D-AD44-427C-B46A-19DDED7B8D0A@microsoft.com...
>> > Hi everyone
>> >
>> > Hope this isn't a silly question (as think I may have made it harder in 
>> > my
>> > head than it needs to be! given all the formulas I'm putting in are
>> > bringing
>> > back errors!!)
>> >
>> > I have one worksheet which gathers info from other sheets and has the
>> > following info
>> >
>> > A         B       C       D
>> > Name   %      %      %
>> >
>> > In another worksheet I have a list of names (column M say M3) next to 
>> > the
>> > names I want to put in a formula that will give me the following 
>> > results:
>> >
>> > If M3 is in worksheet 1 and the corresponding cells to that name in b, 
>> > c
>> > and
>> > d are all >0% write Yes otherwise write No but if M3 doesnt match 
>> > anything
>> > on
>> > the list write - .
>> >
>> > Thanks as always
>> >
>> > Kiwi
>>
>>
>> .
>> 


0
T
2/3/2010 11:04:48 PM
Reply:

Similar Artilces:

restoring GP 10.0 32-bit into GP 10.0 64-bit
Hello: We are going to install GP 8.0 on a 32-bit server in-house and restore a client's 8.0 data into these SQL databases. Pretty cut and dry. Next, we will upgrade this in-house server from 8.0 to 10.0. Continuing, we will install GP 10.0 on the client's new 64-bit server. Finally, we will restore the upgraded 10.0 databases from our in-house 32-bit server to the client's 64-bit server. Here's the question. Is there anything "wrong", from a technical standpoint, in restoring SQL databases from a 32-bit environment into a 64-bit environment? Thanks! chil...

Access 97 Runtime question
We are using Access 97 runtime for some of our staff. I noticed today that in reports if they right mouse click, they don't get a shortcut menu. I want them to be able to Export the report they are viewing. Would Access 2003 runtime have the shortcut menu? Thanks Kelvin "Kelvin Beaton" <kelvin dot beaton at mccsa dot c o m> wrote in message news:uiQA0i7zHHA.1168@TK2MSFTNGP02.phx.gbl... > We are using Access 97 runtime for some of our staff. > I noticed today that in reports if they right mouse click, they don't get > a shortcut menu. I want them to be...

Optimiztion question
I was looking at MSDN on optimizing queries (http://msdn.microsoft.com/en-us/library/ms998577.aspx) and am unclear as to why a foreign key relationship affects the query in this case. At the beginning of the article it gives an example and starts out by saying: The sample screen shot in Figure 14.2 shows an execution plan that accesses only one table, although two tables are included in a join in the query. Because there is a declared foreign key relationship between the authors table and the titleauthor table, and the au_id column in the titleauthor table is not allowed to b...

Access 97 to 2000 conversion problems 08-08-03
I am trying to convert an Access 97 database to Access 2000 but with great difficulty. If I try to load it in Access 2000, the conversion is attempted but on the apparent completion the new database is not saved and the screen is blank. If I try creating a new database and importing everything then this works fine for the data, but no reports or forms are imported. I have tried all the latest patches to MS Office. Any suggestions would be very welcome. ...

GP 10 and Terminial Server 2008
My customer is having issues with GP 10 sp4 and Terminal Server 2008. Whenever they are in one of the Navigation panes (Purchasing) and they choose an option in the navigation Pane other than the PURCHASING (ie VENDORS) they are kicked out of GP. Has anyone come across this? ...

Sales for Outlook Installation 10-24-05
I'm having trouble installing CRM sales for outlook. The installation errors literally on the point of finishing. The message is "Setup has failed because of an error. Setup was unable to install the .msi file for microsoft crm sales for outlook". I have searched the knowledge base and newsgroups to find any info on this matter but at the moment I have struggled to find any signifcant information. The pc used is running XP pro SP2. Thanks for any assistance in advance ...

Macros/Signatures Question
I have an Excel Workbook that I created for a PC Order Form. I set it up to only make certain rows visible based on the Machine Model that they choose. I digital signed the Worksheets and exported the Digital Signature Certificate and installed it on the User’s PCs. The code works great. The user opens the Workbook and it runs the Macros with no problem. The problem I have is when the user goes to save the Workbook. They get an error that “Excel can not sign VBA macros when saving to this file format. Do you want to remove the digital signature and continue saving this workbook?”...

I want to design both side of a post card using publisher 03
I have go side one completed. How do I do the other side??? Insert Page -- Don Vancouver, USA "bettboop" <bettboop@discussions.microsoft.com> wrote in message news:68D78C55-BB03-44EC-97AF-D8FBA74FB95C@microsoft.com... >I have go side one completed. How do I do the other side??? ...

installing GP 10.0 on workstations only
Hello: I understand that you can install GP 10.0 and forego installation of the application on the server. If you choose to go that route, then how do you path to SQL during the first-time run of GP Utilities when you create the company databases? I mean, if you install GP on the first workstation without installing GP on the server at all, you have to tell GP the path to the database (in the Database Setup window). Can you use UNC pathing in that window or do you have to use mapped drives? Also, what sort of security do you need to create the databases from the workstation? Domai...

Macro Question #10
Hi, I am working with a text file that I imported into Excel. It is a transactional report of sales by customer. The data will have the customer number and name spread out over three cells. What I want to do is to create a macro that will contatenate all three cell and then perform a paste special and paste them into another cell in another column. Since the number of transactions by customer can vary the customer name and number can appear anywhere on the page. I created a macro to do this using the first customer which appears in cell B5 through D5. The next customer appears...

Simple IMAP question
Hi, How do I enable shared calendar with free/busy information through IMAP ? When I add the "calendar" folder in IMAP it displays as a mail folder, not a calendar. Pierrot Is this calendar a mailbox or public folder? Why are you using IMAP instead of MAPI? MAPI is much more feature complete as it applies to this sort of thing. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Pierrot Robert" <mcthepro@nospam.hotmail.com> wrote in message news:%23NzXwrV6GHA.4352@TK2MSFTNGP05.phx.gbl... > Hi, > > How do I...

Exchange 2003 Question??
Dear all, I would like to know if there is an upgrade path from Exchange 2003 Standard Edition with SA to Exchange 2003 Enterprise Edition with SA? Thank you. Regards, Kueh. This might help http://support.microsoft.com/default.aspx?scid=kb;en-us;170280 -- Santhosh Sivarajan MCSE(W2K/NT4),MCSA(W2K3/W2K/MSG),CCNA,Network+ "KA Kueh" <kka@ksm.com.my> wrote in message news:exVKwgOKEHA.1040@TK2MSFTNGP10.phx.gbl... > Dear all, > > I would like to know if there is an upgrade path from Exchange 2003 Standard > Edition with SA to Exchange 2003 Enterprise Edition wit...

Extender problem with GP 10
Hi all, I setup a Dynamics GP 10 system for a customer. The system is used from HRM staff. I used Extender to add a number of extender windows and detail windows. On the Sever all users can access the extender windows but when they login on a separate terminal server (with a client installation) the Extender windows are not accessible. Only the Extender Detail Windows can be seen. When trying to go to the windows all users including sa get the following message: "Not all required fields have been entered for the window <window name>." I went in the extender and opened ...

question about leaving messages on server
My husband and I have 1 POP mail account. I am accessing it from a computer in Virginia, where I'm staying with relatives. He is accessing the same account from California. I have my Outlook set up to keep messages on the server, but to delete them after 1 day (so that he will still get the messages, but the server won't get clogged). His email program, Eudora, is set up so the messages will be deleted from the server after he downloads them. Is there a chance I won't get some messages because he has his computer set up that way? (He downloads it 1st, then it gets deleted off serv...

Questions on migration from VS2005 to VS2010
Hi folks, As is the case every couple of years, I go through a nerve-wracking transition to a new development environment. The thing is, I've become really comfortable with VS2005, and our small team has a very simple and effective approach to development. I am nervous that VS2010 will negatively effect our productivity; but don't want to get too "far behind" toolset versions. So, that being said, I wanted to ask a few questions on what to expect when moving from VS2005 to VS2010 on an upcoming project. Your feedback is highly appreciated. Question...

Filtering question
Is there a way to expand the filter option so that it shows 1 row (or more), both above and below any row that contains the value I am looking for? Data|filter|autofilter examines each cell/row on its own. Maybe you can put the key value for each group on the rows you want. Then filter by that column. richzip wrote: > > Is there a way to expand the filter option so that it shows 1 row (or more), > both above and below any row that contains the value I am looking for? -- Dave Peterson ...

New to Money 2006
I was using Money 2004 and upgraded it to 2006 last night (God only knows, why :)). I started to hate the idea of Passport and Web Publishing my account details and so did not choose the Passport option. I was able to install with regular Money stand alone password and still update all my accounts by downloading them from the respective financial institutions (as I used to do with 2004). The problem is with MBNA America. I want to download the credit card transactions. But the online setup process is asking me for passport setup. I looked at the description of this bank on Microsoft site...

upload form problem / server question
www.icingpictures.co.uk / win xp / frontpage 2003 Want to create a form with upload file form field - i read on this forum that the server type i use is important - it should be a windows type server with frontpage extensions - i currently use a linux server with front page extensions - do i need to change to a windows server? Thanks for taking a looka t my question. Joyce Yes, you must have a Windows Server, just like you stated you read. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "threademporium" <threademporiu...

Outlook 2003 question #4
Hi ... I have 4 email accounts with outlook 2003 and they are all from the same isp provider ... My problem is when i am going to create an email to someone i can't tell which email account is sending the email to , its usually set a default ... What i mean is that when i view my email accounts they are listed as so and so default , # 1 , # 2 , # 3 , I don't know what 1 or 2 or 3 are ... Is there a way to notice which email account is which beside having numbers beside them ... I can't tell which is which ... It would be better if you could view the actual name of the ema...

Tax Collected Question
Is there a report to run that will show the tax that has been paid by the customer and not on the tax that was collected via a tender? Or is there a report to run that shows invoices have been paid by check, credit card, or cash that leaves out those tendered to account? We would like to be able to pay taxes on what has been paid to us and not what we are waiting on for payment. hi send me your email I will forward you some interesting reports for your below issue. "bevans22" wrote: > Is there a report to run that will show the tax that has been paid by the > custome...

Questions regarding Data center operation
Hi, I have over 8 years of IT experience but very new to the Data Center space and have 4 questions. If someone can answer, I would really appreciate! You can consider these questions more of a brainstorming ideas. 1. If a facilities capacity is re-classified as 5 MW lower than it was previously, what needs to happen here? 2. We need to expand Network capacity at a facility by 2 times but for only 20% more money, how can we do it? 3. Where to start if you are responsible for tech refresh in existing facility? 4. During tech refresh planning you find out about a delay in delivery...

Excel 2003 Worksheet formatting questions
I have created a simple income daysheet for my medical practice. The idea is for all money collected and received in a day to be inputted in that day's day sheet along with the source of the money, why it was received and if there is a check or statement number associated with the payemnt for it to be recorded. My questions are: 1. Column A is for date - I want to automatically enter the current date in each cell in column A. So when a new payment is being entered the current date is automatically entered for the payment clerk. I can get the date in 1 cell using the date functio...

Custom Function 04-25-10
I am trying to create a tool for yearly employee performance reviews: each employee is ranked on 5-10 different "competencies" and their behavior related to those competencies throughout the year. I have a table tracking those events. At year end I would like to create a report bound to a query that groups the employee by competency, avg rank for the competency and lists each observed behaviour for the entire year in its own field. The tblDailyObserved table lists Empname, Competency, Edate, Behavior, Rank EmployeeN Competency Rank Behavior John Smith Customer F...

question about ICON of frame display?
i use a customized icon for my app. and everthing is ok except that display in caption bar. i mean that it can be displayed in task bar, but for caption bar, it always is substituted by default app icon. so, how to display the customized icon in caption bar just like display in task bar. ps: there are 3 formats of size images in the customized icon, they are 16*16, 32*32, 48*48. and they are all true color. -- Frank F.Han +-----------------------------------------+ | winsays@:-)hotmail:-).com | +-----------------------------------------+ Did you create both a 32x32 and 16...

Sort & Page Setup Question
Hopefully this is the correct forum for my question/problem. I am trying to create a songbook that includes both an Artist Name (Column A) and Song Title (Column B). What I would like to do is have the artist name column continue on the same page in a third column (Column C) and the song title column continue in a fourth column (Column D). So, as an example, Artist Names beginning with "A" run down the first colum and continue alphabetically in the third column and then start on the second page in the first column and so on. The best way I can describe the format I want is...