If statement- formula

Well I'm stumped again.

If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, 
otherwise enter contents of close!ae2:ae19999

thanx,
~Julz

0
jhbiz (27)
8/1/2004 4:04:15 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1096 Views

Similar Articles

[PageSpeed] 42

Hi
what do you mean with 'enter contents'?. Do you want to add them? If
yes try
=SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199
99,"<>xyz",close!ae2:ae19999)


--
Regards
Frank Kabel
Frankfurt, Germany


Julz wrote:
> Well I'm stumped again.
>
> If close!ag2:ag19999 contains "xyz" enter contents of
> close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999
>
> thanx,
> ~Julz

0
frank.kabel (11126)
8/1/2004 4:23:36 PM
That's it!!  Thanx!!

Just when I think I know what I'm doing...
I can't figure this out.

if D=N then count k<16



Frank Kabel wrote:
> Hi
> what do you mean with 'enter contents'?. Do you want to add them? If
> yes try
> =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199
> 99,"<>xyz",close!ae2:ae19999)
> 
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> 
> Julz wrote:
> 
>>Well I'm stumped again.
>>
>>If close!ag2:ag19999 contains "xyz" enter contents of
>>close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999
>>
>>thanx,
>>~Julz
> 
> 

0
jhbiz (27)
8/1/2004 5:41:22 PM
Hi
not sure what you're aksing with your new question. pleae some more
details :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Julz wrote:
> That's it!!  Thanx!!
>
> Just when I think I know what I'm doing...
> I can't figure this out.
>
> if D=N then count k<16
>
>
>
> Frank Kabel wrote:
>> Hi
>> what do you mean with 'enter contents'?. Do you want to add them? If
>> yes try
>>
=SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199
>> 99,"<>xyz",close!ae2:ae19999)
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> Julz wrote:
>>
>>> Well I'm stumped again.
>>>
>>> If close!ag2:ag19999 contains "xyz" enter contents of
>>> close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999
>>>
>>> thanx,
>>> ~Julz

0
frank.kabel (11126)
8/1/2004 5:57:18 PM
This is what I was trying to do.  Seems we had already gone down that 
road.  I'm just battin' a thousand today.  ;)

=SUMPRODUCT(--(open!D2:D19000="n"),--(open!K2:K19000<16))

Thanx as always and please forgive my redundancy.  I'm in over my head 
and I know it.

~Julz

Frank Kabel wrote:

> Hi
> not sure what you're aksing with your new question. pleae some more
> details :-)
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> 
> Julz wrote:
> 
>>That's it!!  Thanx!!
>>
>>Just when I think I know what I'm doing...
>>I can't figure this out.
>>
>>if D=N then count k<16
>>
>>
>>
>>Frank Kabel wrote:
>>
>>>Hi
>>>what do you mean with 'enter contents'?. Do you want to add them? If
>>>yes try
>>>
> 
> =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199
> 
>>>99,"<>xyz",close!ae2:ae19999)
>>>
>>>
>>>--
>>>Regards
>>>Frank Kabel
>>>Frankfurt, Germany
>>>
>>>
>>>Julz wrote:
>>>
>>>
>>>>Well I'm stumped again.
>>>>
>>>>If close!ag2:ag19999 contains "xyz" enter contents of
>>>>close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999
>>>>
>>>>thanx,
>>>>~Julz
> 
> 

0
jhbiz (27)
8/1/2004 6:22:29 PM
Hi Julz..........

Considering D, N, and K are Rangenames,
Maybe..........
=IF(D=N,COUNTIF(K,"<16"),"")

Vaya con Dios,
Chuck, CABGx3




"Julz" <jhbiz@austin.rr.com> wrote in message
news:u$UEE7#dEHA.592@TK2MSFTNGP11.phx.gbl...
> That's it!!  Thanx!!
>
> Just when I think I know what I'm doing...
> I can't figure this out.
>
> if D=N then count k<16
>
>
>
> Frank Kabel wrote:
> > Hi
> > what do you mean with 'enter contents'?. Do you want to add them? If
> > yes try
> > =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199
> > 99,"<>xyz",close!ae2:ae19999)
> >
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > Julz wrote:
> >
> >>Well I'm stumped again.
> >>
> >>If close!ag2:ag19999 contains "xyz" enter contents of
> >>close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999
> >>
> >>thanx,
> >>~Julz
> >
> >
>


0
croberts (1377)
8/1/2004 6:27:08 PM
Reply:

Similar Artilces:

I need help with a formula
Hi everyone I'm making a stock list sheet and I like to put on it the value - and + .. like when I take boxes out it will give me the total and when I add in it will change also. This is for a weekly count. I'm very new to excel so please H-E-L-P-. I need the instruction like u give it to a child. :) :) Thanks a lot -- pampam ------------------------------------------------------------------------ pampam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26224 View this thread: http://www.excelforum.com/showthread.php?threadid=395241 We need ...

Formula to count the number of boxes checked
Hello, In Excel a form was created where check boxes were created within cells F8:F27. I would like to add a formula at the bottom of the form to count the number of boxes that are checked. I've tried using the countif formula, but I don't know how to tell it to count the boxes with a check mark Can anyone help me, please? -- Thanks, Annie If the checkbox is from the control toolbox; in design mode right click the checkbox >properties and set the linked cell of each check boxe to its cells and try the formula in the total cell =COUNTIF(F8:F27,TRUE) ...

Formula or Macro needed?
Hi, I need some help desperately, for reasons which are too complicated to go into right here and now, I need a formula or a macro to produce an excel spreadsheet of 16 columns and 16 rows where each row and each column contains the numbers 1 through to 16 without the same number repeated on any row or any column - I know it may sound a little tricky :eek: but I'm hoping somebody is up for a challenge like this and can help rather quickly - I'm not interested in the number of permutations or anything daft like that - I just need a solution. Thanks to anybody who knows and can post...

IIF Statement Definition
Hello, Would someone explain to me what exactly the below is doing? I understand everything up to the "Mod 2)...." Thanks! IIf((Sum(IIf([Day1] Is Null,1,2)) Mod 2)=0,Sum([Day1]),"SA") -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1 IIf((Sum(IIf([Day1] Is Null,1,2)) Mod 2)=0,Sum([Day1]),"SA") IIf([Day1] Is Null,1,2) This part will return 1 if Day1 is Null; otherwise it returns 2 (Sum(IIf([Day1] Is Null,1,2)) The values returned are then summed for the records If the number returned is an even nu...

Rounding off Formula.
I have a sample here in CELL-A1 with the number 16 and in CELLs (see below)that all add up to 16. B1-5.05 B2-6.75 B3-4.20 Now the thing is I would actually like to have a formula that can do something like this. B1-5.00 (it would loose .05) B2-6.75 (left alone since its already .75) B3-4.25 (it would gain .5 to add into .25 since .20 is greater than .05) moving the numbers accordingly so that it still adds up to 16 and keeps the numbers ... in .25 .50 .75 etc.only. Any help would be greatly appreciated. Thank you very much. Koji Hi Koji You can use the following formula to convert th...

excel formulae #5
I want to take out 90% of the rows, in order to have a more workable number of rows. How do i eliminate rows at random, i.e. not me selecting? Try the macro below, which will keep _about_ 10% of the rows - that the nature of randomness ;-) HTH, Bernie MS Excel MVP Sub Keep10ishPercent() Dim myRows As Long Range("A1").EntireColumn.Insert Range("A1").FormulaR1C1 = _ "=IF(RAND()*10<=1,""Keep"",""Trash"")" myRows = ActiveSheet.UsedRange.Rows.Count Range("A1").Copy Range("A1:A" &...

formula question #27
I have an array of numbers like 100 0.01 200 0.015 300 0.02 400 0.025 500 0.03 say the range name is tab1 and an array of numbers like (call it tab2) 150 250 350 I want to look up the numbers from tab2 in tab1 and multilply it by the 2nd column from tab1 and add up the result. So, I want 150*.01+250*.015+350*.02 I tried as an array formula =sum((vlookup(tab2,tab1,2)*tab2) but this does not work. What should I be doing? TIA Say your tab2 info in col. A. In col. B type vlookup(A1,tab1!$A$1:$B$5,2,TRUE) Note: Use "true" in the vlookup, since your values in tab1 col. A and ...

Excel 2002 Formulas Book!!!
Hi All, Can anyone help please??? I purchased 'Excel 2002 Formulas' by John Walkenbach a couple of weeks ago. However, and I don't know how, but I appear to have lost the companion CD-ROM. Does anyone have a copy that they could possibly copy and send to me?? I would be wiiling to pay any costs, postage etc.. Or even a p2p fileshare folder that I could download it from??? (I use DC++). I've been in touch with the store where I puchased the book, but they were pretty useless to be honest. Any and all help would be most appreciated. Regards, Patrick. --Remove 'spam...

copy worksheet with formulas but not data
I have a worksheet with formulas that I need to copy for each month. The worksheet is filled in with data, but I only want the formulas, lables, headings, formats etc not the actual data. How can I do this? Any help is appreciated greatly. How about copying the worksheet exactly and then clearing all the data? After copying, enter and run this small macro: Sub clearit() Dim r As Range For Each r In Selection If IsNumeric(r.Value) Then If r.HasFormula Then Else r.Clear End If End If Next End Sub It will clear numbers, but l;eave formulae and text alone. -- Gary's Student...

download statement
I am trying to download a statement using money 99 and receive the following error. "bdsie12029" any ideas? the message is an internet error. I use dsl and can update files on my money home page. You can no longer setup your online services using the branding server. Which bank are you using? It may be possible to set it up manually if your bank had sent you instructions that included a Financial Institution ID. You would perform the manual setup by going into step 2 and choosing to use online setup files. Then browse to the BDS folder in the folder where you installed Money t...

Need formula #3
Hi, I am doing a shipping form. If a user enters text in the total cost field, the Item # field should automatically show 1 (for the first line item). But if they need to use row 2 for an additional description of the item in row 1, and then they enter the 2nd item in row 3, I need that to show a 2 in the Item # field indicating that it's the 2nd line item. The way this will know is that if there's nothing entered in the Cost field, it's obviously not it's own line item. Hope I've made my question clear. Anyone know how this can be done? Thank you, Tracey ---------...

Excel Formula Error for No Good Reason
I don't know why, but this formula will not stop giving me an error in Excel 2003. =IF( $A8 <> "", B8 * E8, IF( ROW(H7) <> 1, IF( G8 = "Subtotal", SUM( H$2:H7 ), IF( LEFT(G8, 3) = "Tax", ROUND( $J$1 * H7, 2), IF( G8 = "Total", INDIRECT( ADDRESS( ROW(H8)-2, COLUMN(H8) ) ) + H7, IF( G8 = "Depr", SUM( INDIRECT( "G2:"&ADDRESS( ROW(H8) - 4, COLUMN(G7),4) ,TRUE) ), "" ) ...

Formula to average ignoring negatives?
What formula would I use to average a group of numbers and ignore the negatives? For instance, if I'm averaging +8 and -6, I want the result to be 7, not 1. Thanks! That's not really ignoring the negatives, but this worked ok for me: =AVERAGE(ABS(A1:A10)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) If you really wanted to ignore the negatives (pretend that they didn't exist): =AVERAGE(IF(A1:A10>0,A1:A10)) This is also an array formula. Stev...

Formula for Searching & matching two values in excel
Hello I wonder if anyone can help with my problem.... I need to create a lookup to match two values in one worksheet with two values in another sheet, returning a third value: So I have two worksheets in excel Projects and Orders. To match these sheets, the Orders page contains lots of duplicate "Order Codes" which are numbered between 1 & 20 called "Line item". These Order line items each refer to a particular Project on the Projects sheet but can only be traced to a Project by searching the Projects sheet for Order number then Line item. So for instance Order...

Getting rid of the formula but keep the Value
I have created a PRODUCT formula linked with certain cells and inputed the values elsewhere on the spreadsheet. However, whenever I cut and paste the values on another worsksheet or program, the formula shows up and not the value. Is there a way to keep the value when transferring it to another location? Copy>Paste Special>Values should work. >-----Original Message----- > >I have created a PRODUCT formula linked with certain cells >and inputed the values elsewhere on the spreadsheet. > >However, whenever I cut and paste the values on another >worskshee...

Need an SQL statement please
Could someone please me how I can delete all items that have no description with item? Created these items from Percon download - and I know you guys can easily tell me what SQL statement should have in it...thanks in advance. Always backup your database before executing any UPDATE, INSERT or DELETE query. Execute any query at your own risk. To test which records will be affected, try this: SELECT ItemLookupCode, Description from ITEM were DataLength(Description) = 0 If the result contains the items you wish to delete, try this: DELETE FROM ITEM were DataLength(Description) = 0 Here...

Money 2007 not downloading statements automatically.
The update schedule is set to "Every Day" for all of my updateable accounts, but it simply will not update unless I manually click "Update Now...". No automatic updates. I have money open almost continuously and it still doesn't work. According to help, "To use automatic online updates, you must do both of the following: a.. Save your bank sign-in information in Money. Otherwise, you will need to enter an account's sign-in information every time you want to update the account. (Some banks will automatically save your sign-in information.) b.. Sign in t...

How I build a formula where I just consider from a data base only.
Im trying to build a formula to sum all the negatives values in a data base. PLease help me with this. In article <5D7C4679-5F8D-4A5D-906B-1E0A4E3FDD61@microsoft.com>, "Wilnelia" <Wilnelia@discussions.microsoft.com> wrote: > Im trying to build a formula to sum all the negatives values in a data base. > PLease help me with this. =SUMIF(A1:A10,"<0") Hope this helps! Example: Sum all negative values in col. A. =SUMIF(A:A,"<0") HTH Jason Atlanta, GA >-----Original Message----- >Im trying to build a formula to sum all the nega...

Absolute Formula Help
A1 on Sheet2 =Sheet1!A1 I am trying to make this formula absolute, so if I were to move,cut, or copy A1 on Sheet1, then A1 on Sheet2 will display what ever is in A1 on Sheet1. For some reason it keeps changing them every time I cut and paste A1 on Sheet1 to where ever. I've tried all these ($A$1, $A1, A$1, $A1) and nothing works? Can anyone Help? Thanks. Try: =INDIRECT("Sheet1!A1") Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6...

Incrementing Formula by Column Instead of by Row
In one worksheet I have the formula: =SUM(Data!B5:B78) referencing data in a column on another worksheet. I want to drag the formula down to the next row so that it will read: =SUM(Data!C5:C78), the row after =SUM(Data!D5:D78)...etc. Without dollar signs it increments by row as it should. When I add in dollar signs like so: =SUM(Data!B$5:B$78) and I try to drag it down, it just copies that exact formula to the cell below it like I typed: =SUM(Data!$B$5:$B$78) even though I didn't. How do I get Excel to do what I want. I am using Excel 2003. I've searched everywhere...

array formula to provide all values associated with a given value
From an Access Query. I've got a list with the unique ID's for reports, and the next field indicates what Language they were written in. This was from a Many - Many relationship in Excel, and many of the reports were written in many different languages. ReportID CountryID 1000020 Td 1000020 Tz 1000020 Ug 1000020 Zm 1000020 Zw 1000035 Ao 1000035 Mz I've got over 10,000 records like this and would like an array formula that for each Report ID (and i'...

Excel 2003 IF Statement Help
At my work when new hires are going through training, all of the work they complete needs to be entered in an Excel spreadsheet so we can track whether their decision, rate integrity, letters and processing are correct. We then are able to do a rollup that can see if they are meeting a certain % correct. Currently, my IF statement is as follows: =SUMPRODUCT(('Jury Sheet'!$D$3:$D$1000="Auto")*('Jury Sheet'!$K$3:$K$1000="Correct")) The first IF statment sorts through the different types of work (Auto, Homeowners, Umbrella, VPP, Renters). In ...

Mid month financial statements
I need to produce a balance sheet and P&L financial statements as of 3/7/05. Is there a way to tell FRx to capture transactions between specific dates (3/1 to 3/7) and not the full month? We use Great Plains 7.0, FRx 6.5 Susan, You can use the start and end dates in the column setup to restrict the transactions that show up on your reports. Depending on the type of the report and what columns you have, the exact solution will be slightly different. For more details, go to help in FRx and type in "Restricting Columns to Specific Dates" in the search - I don't hav...

IF OR Formula
Hi everyone, hope you are all well I'm having a slight problem with a formula that I've written =IF(B2="","",IF(ISNUMBER(VALUE(LEFT(B2,6)))=TRUE,"",B2)) This would be placed in A3 then copied down all cells in the A column, the following is an example of what would be in the other cells (just so you get an idea what i'm trying to do) B2 = Joe Bloggs B3 = 381034 Mary Smith B4 = 345876 John Smith B5 = |spaces| Lunch 12:00 13:00 B6 = |spaces| Lunch 12:30 13:30 and so on. The prob I'm having is that the a...

Auto Extend Formulas doesn't work for a particular formula
I'm using Excel 2003 and in a particular worksheet, the Auto Extend Formulas doesn't work on a particular formula. I've turned on the Auto Extend option and it does work on another formula in the same worksheet. The problematic formula is: =WEEKNUM(DATEVALUE(TEXT(A165,"dd/mm")),2)-37+52 Can someone kindly help explain why there's a problem here? What do you have in A165. "niwrad" <niwrad@discussions.microsoft.com> wrote in message news:A7EC7AB7-58B3-4171-8EF7-C236FBE502FE@microsoft.com... > I'm using Excel 2003 and in a particular works...