using if function..please help

i am trying to use the if function in the following manner.  

A                  B
apple            red
apple            red
grapes          purple
grapes          yellow
star              n/a

if(B1:B5="n/a",0,countif(A1:A5, "star")

I want to use this formula not only for 'star' but also for 'apple' and 
grapes' and many more such fruits which might have n/a later.  But, everytime 
I use this formula it returns a 1..should it not return a 0 for when the 
value in B is n/a?

Please help..
Thank you
0
Utf
3/17/2010 4:58:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
791 Views

Similar Articles

[PageSpeed] 9

Are you trying to say that if any cell in the range B1:B5 contains "n/
a" then you want to return zero? If so, try it this way:

=3DIF(COUNTIF(B1:B5,"n/a")>0,0,COUNTIF(A1:A5,"star"))

Hope this helps.

Pete

On Mar 17, 4:58=A0pm, sd <s...@discussions.microsoft.com> wrote:
> i am trying to use the if function in the following manner. =A0
>
> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B
> apple =A0 =A0 =A0 =A0 =A0 =A0red
> apple =A0 =A0 =A0 =A0 =A0 =A0red
> grapes =A0 =A0 =A0 =A0 =A0purple
> grapes =A0 =A0 =A0 =A0 =A0yellow
> star =A0 =A0 =A0 =A0 =A0 =A0 =A0n/a
>
> if(B1:B5=3D"n/a",0,countif(A1:A5, "star")
>
> I want to use this formula not only for 'star' but also for 'apple' and
> grapes' and many more such fruits which might have n/a later. =A0But, eve=
rytime
> I use this formula it returns a 1..should it not return a 0 for when the
> value in B is n/a?
>
> Please help..
> Thank you

0
Pete_UK
3/17/2010 6:39:07 PM
Only a few Excel functions accept ranges. If isn't one of them. You probably 
want:
=sumproduct((a1:a5="star")*(b1:b5<>"n/a"))

Regards,
Fred

"sd" <sd@discussions.microsoft.com> wrote in message 
news:5C128C12-EF81-42E2-A028-08E8521909FC@microsoft.com...
>i am trying to use the if function in the following manner.
>
> A                  B
> apple            red
> apple            red
> grapes          purple
> grapes          yellow
> star              n/a
>
> if(B1:B5="n/a",0,countif(A1:A5, "star")
>
> I want to use this formula not only for 'star' but also for 'apple' and
> grapes' and many more such fruits which might have n/a later.  But, 
> everytime
> I use this formula it returns a 1..should it not return a 0 for when the
> value in B is n/a?
>
> Please help..
> Thank you 

0
Fred
3/17/2010 6:48:24 PM
Reply:

Similar Artilces:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Index and Match Function in VB
A few days ago, I did receive very good help from Max and Dave to achieve the following formula, =IF(B5="Synth",INDEX(AUTOS!$K$10:$K$500,MATCH(1,(Autos!$F$10:$F$500='ID'!D5)*(Autos!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="C"),0))-INDEX(AUTOS!$K$10:$K$500,MATCH(1,(AUTOS!$F$10:$F$500='ID'!D5)*(AUTOS!$G$10:$G$400='ID'!$R$2)*(AUTOS!$I$10:$I$400="P"),0))+$R$2-$R$5,VLOOKUP(D5,$P$4:$R$8,3)-$R$5) Basically I was looking to retrieve data based on multiple criteria and the formula is working just fine. I now want to also be able to def...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Function is killing performance
Hi - I'm using a custom function in a query and it takes forever - was hoping someone might have an idea of how to speed things up a bit. A little more info: the function is relatively simple (4 arguments that are run through an IF statement to pick a value from another table (only 12 records). The query is run against a table with 41k records. The numbers don't seem to big here, and the query takes over an hour. Any ideas?? Where & how are you using the IF's? Sounds like a coding problem to me Pieter <stephen.h.dow@gmail.com> wrote in message news:1189019241.93425...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

PROPER function
Hi all - Stupid me, maybe, but I can't get this function to work. The help file says: "PROPER (text) Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize." So the text I want to convert is in cell, say, I370. In helper cell J130 I enter =PROPER(I370) But all I get in cell J130 after hitting enter is =PROPER(I370). In other words, it doesn't seem to be acting as a formula --- it is just appearing literally. This is an inherited spreadsheet, so it's possible something ...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

slow sync with Calendar using Kyocera 7135 via Outlook 2003
I'm stuck and need help. I have a Two Day Old Brand New Dell Dimension 4600. Does anyone out there know what to do when Outlook 2003 is giving a Log report that reads that Outlook Calendar - Slow Sync synchronization failed? This is a first time occurence for me. Outlook also reported that a device is trying to get access to my E- Mail addresses which may indicate a Virus may be present. I've run my McAfee Virus Protection and it didn't detrect anything. I'm at my wits end and don't know what else to do. Can anybody help? ...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

can not Send/receive using Outlook 2003
Just bouhght new computer and am loading all of my software that worked onmy old one. When I try to send and recieve I get the message object not found. I have used test and this work ok. I have also reloaded all of my broadband and office software numerous times to no prevail. Software Windows xp Office 2003 ADSlL broadband with bigpond Gp Check to make sure the accounts are setup correctly if they are then run Detect and Repair, still an issue then create a new profile. May also want to check your anti-virus software program setting for outgoing mail and disable that option. Rega...