Not entirely sure if the IIF statement is the correct way to go about this, I have a field on a query that has total pounds of either printed or plain product. I want to add a column to my query that only adds up the printed product. So, if "product type" is like printed, then add up a total, is basically what I am trying to say. Can you help me with the proper formula? -- Message posted via http://www.accessmonster.com

0 |

2/19/2010 2:37:39 PM

Assuming you have: - a Text field named product type - a Number field named pounds put a text box in the Report Footer section, and set its Control Source to: =Sum(IIf([product type] Like "*printed*", [pounds], 0)) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jligue via AccessMonster.com" <u56071@uwe> wrote in message news:a3deb48537c8f@uwe... > Not entirely sure if the IIF statement is the correct way to go about > this, I > have a field on a query that has total pounds of either printed or plain > product. I want to add a column to my query that only adds up the printed > product. > So, if "product type" is like printed, then add up a total, is basically > what > I am > trying to say. > Can you help me with the proper formula?

0 |

2/19/2010 2:43:18 PM

Formula is merely an if if(B2 > 0,1,0) could also probably use countif nb: I can't use a summation here as some of my macro's use differen filters in combination with subtotal and sums alread -- shaw ----------------------------------------------------------------------- shawb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1420 View this thread: http://www.excelforum.com/showthread.php?threadid=26268 Hi if this formula is ion column C (cell C2) try: =IF(OFFSET(C2,0,-1)>0,1,0) or --(OFFSET(C2,0,-1)>0) But I'm quite sure you could use a ...

I need some help: In this example I have a column of 5 cells B1:B5. I also have another cell C1. I would like to have cell C1 to display the most recent entry in the B Column. Start B1=Null C1=Null B2=Null B3=Null B4=Null B5=Null First Entry B1=4 C1=4 B2=Null B3=Null B4=Null B5=Null Second Entry B1=4 C1=7 B2=7 B3=Null B4=Null B5=Null Third Entry B1=4 C1=3 B2=7 B3=3 B4=Null B5=Null Fourth Entry B1=4 C1=0 B2=7 B3=3 B4=0 B5=Null Fifth Entry B1=4 A1=9 B2=7 B3=3 B4=0 B5=9 Is there a fo...

I've got data from two different spreadsheets, but need the product cos from Data Range A to put next to the sales price of Data Range B. Data Range A Model Number Product Cost A 0.10 B 0.20 C 0.30 Data Range B Model Number Size Sales Price A 8x10 $1.00 B 5x7 $2.00 C 4x6 $3.00 What formula do I use if I want to get the SALES PRICE from Range B an put it right next to the Product Cost -- Message posted from http://www.ExcelForum.com Hi Try using the following using your proper filenam...

Hello everyone, Basic question: Is there a set quideline on when to use quotes in formulas and array formulas? -- Thank you... Elaine Hi! Can you be more specific? You must use quotes when referencing text values: =IF(A1="Red","yes it is","no it isn't") =SUMIF(A1:A10,"Ten",B1:B10) Do not use quotes when referencing numbers: =IF(A1=10,10,0) =SUMIF(A1:A10,10,B1:B10) Biff "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:5879DD53-B927-401E-8304-3D158C51B232@microsoft.com... > Hello everyone, > > Ba...

Hi all, I have price data in rows like this (each price takes up a cell): 5 5.43 6 6.25 6.50 8 15 9.25 9 8.75 8.50 8.50 8.50 8.50 13 I need to show trends via conditional formatting where on 3 or more consecutive days prices fluctuate EQUALLY by 0.25 or more each time, up or down... IE, with the above data, I would want to "flag" let's say, in red fill color, the sequence 6, 6.25, 6.50 as well as the group 9.25, 9, 8.75, 8.50, but NOT the group of 8.50, 8.50, 8.50. I have tried if/and/or statements again and again and come close on simpler tre...

For some reason, the when I insert a trend line into a graph, the formula comes up with one part of it in scientific format (y = 2750.2x2 - 64597x + 2E+06). I konw the last expression in the formula is $2,000,000 but this is rounded- and I need to know what the exact amount is. I have tried extending the formula box, but that doesn't work. Can anyone help me? Thanks in advance. See www.stfx.ca/people/bliengme/ExcelTips on the use of LINEST to get the values into cells - then you can have what ever format you need and there is no need to copy the values. -- Bernard Liengme www....

I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...

If I have a value of $10 and add 10% by dividing by 0.90, I get $11.11 which I would like to round up to the nearest $0.5, i.e. $11.15. Does anybody know the formula and can you use it on a result which is also a formula? Thanks, Lee "Lee" wrote: > If I have a value of $10 and add 10% by dividing by 0.90, > I get $11.11 which I would like to round up to the > nearest $0.5, i.e. $11.15. From your example, I presume you mean "nearest $0.05" (nickel). > Does anybody know the formula and can you use it on a > result which is also a formula? If you truly ...

I'm trying to get a formula that if any one of the cells in A3:A9 are "No" then add the corresponding B3:B9, if "Yes" then don't add. I have six differenting connecting sum areas that all need to be combined in one formula. How can I do this? -- JustLearning =SUMIF(A3:A9,"No",B3:B39) -- David Biddulph "Cindi" <Cindi@discussions.microsoft.com> wrote in message news:B7FDD15D-C14C-4E27-85A9-2E7AC14E2AA2@microsoft.com... > I'm trying to get a formula that if any one of the cells in A3:A9 are "No" > then...

OFFSET(C6,COUNT(C7:C33),0) I am using this formula as part of a spreadsheet I am using for a social golf club, it has 20 sheets, and this formula keeps track of how many games an individual plays. It works well except for the fact if a player misses a game the result (in C35 shows 0, as per the formula) eg: c7 1 c8 2 c9 3 c10 4 and the result in c35 is 4 ( as I want) but if c7 1 c8 2 c9 (no input,blank) c10 3 the result in c35 is 0 not 3 So what I need is to change the ,0) bit but I am not sure what with or how to replace it so that it ignores a blank cell ......... Thanks for any help Ke...

In excel you can only have 7 nested if statements in one cell, i need a 10 nested if statement and my if statements are below Cell A1 =IF(B17>=0,B2+(Input!B2-B17)/Input!B2,IF(B17+C17>=0,C2 -1+((Input!B2-B17)-C17)/Input!B2,IF(B17+C17+D17>=0,D2 -1+(((Input!B2-B17)-C17)-D17)/Input!B2,IF(B17+C17+D17+E17>=0,E2 -1+((((Input!B2-B17)-C17)-D17)-E17)/Input!B2,IF(B17+C17+D17+E17+F17>=0, F2-1+(((((Input!B2-B17)-C17)-D17)-E17)-F17)/Input!B2,IF(B17+C17+D17+E17+F17+ G17>=0,G2-1+((((((Input!B2-B17)-C17)-D17)-E17)-F17)-G17)/Input!B2, IF(B17+C17+D17+E17+F17+G17+H17>=0, H2-1+(((((((Input!B...

I need to do a formula as follows. If the amount in column A is < $1500 calculate it by .034. If the amount is > $1500 then they get .00 of only the amount over %1500. Know how to do IF statements but can't seem to figure out how to calculate only on the amount under a certain value and not on the amount over that value. So results should be $1500*.034 = $51.00 00-1700 *.00 = $00.00 Total Commission $51.00 Help!!!! Thanks if you can do this.:confused: :rolleyes: -- Sharon D. ------------------------------------------------------------------------ ...

IF I HAVE THE OPTION TO PUT ANY OF THE FOLLOWING NAME IN CELL A1 TRACTOR ( REFERS TO $80) BOBCAT( REFERS TO $50) LABOR (REFERS TO $28) AND I HAVE THE FORMULA =SUM(A1*B2) IN CELL C1 HOW CAN I REWRITE THIS FROMULA TO DISTINGUISH BETWEEN TRACTOR, BOBCAT AND LABOR? PLEASE HELP ME =((A1="TRACTOR")*80+(A1="BOBCAT")*50+(A1="LABOR")*28)*B1 -- HTH Bob Phillips "ELISA@RELIABLECONTRACTORS.BIZ" <ELISARELIABLECONTRACTORSBIZ@discussions.microsoft.com> wrote in message news:1185FF6C-3C98-4AA8-907E-AA3001C207E0@microsoft.com... > IF I HAVE THE OP...

A B 1 112233 Expired 2 113344 Valid 3 116655 Valid 4 117799 N/A This Formula is to calculate the number of employee in the column. =COUNTIF(A1:A4,"*"). Can I make it =COUNTIF(A1:A4,"*")- N/A. I mean I want it the formula to calculat the number of employee but if the Column B has the work N/A. include it in the calculation. Try =COUNTA(A1:A10)-COUNTIF(B1:B10,"N/A") 'If you want to count only if there is a numeric value in colA then you may try =SUMPRODUCT((ISNUMBER(A1:A100))*(B1:B100<>"N/A")) -- Jacob (MVP - Ex...

I am linking between two sheets and the link formula is showing up instead the answer. It could be a few things... #1. The cell is formatted as text. Format|cells|General (or some number variation--just not Text) then hit F2 followed by enter to re-enter the formula #2. You're looking at formulas Tools|options|View tab\ Make sure Formulas isn't checked #3. Maybe a mistake in your formula. Make sure that there is no space before the initial = (equal sign) BalancedSolutions wrote: > > I am linking between two sheets and the link formula is showi...

Hi, I'm trying to figure out how to add one more layer of complexity to my formula, but it's not working. Any assistance would be appreciated. My spreadsheet has several expense sections, each with a "subtotal". I'm trying to lookup the subtotal lines and sum for the column in which this formula lies. Here's the simplified version: =SUMIF (Range1, "Subtotal", Range2) This formula works great, but people keep typing the word "Subtotal" differently. So I'd like to replace that string with: IF(NOT(ISERROR(FIND(&...

aack.. I went to 2007.. so far, ok- one I can't solve probelem. I have a cell with conditional formatting.. in part it reads =VALUE(RIGHT(FE2,4)) < 1-FE$84 time was (2003) if I selected that cell, copied-- went to another cell, paste special, formats then the 'fe' '2' 'FE' but NOT the '84' parts above would change relative to the new location so if I went one cell down, one cell right I'd get =VALUE(RIGHT(FF3,4)) < 1-FF$84 now when I do this same paste special, I'm getting all original values-- as if they all had $ in front of them ...

Hi all, my name is Sebastiaan of Holland and i could sure use your help!!! Thanks for taking some of your time to give me some advice, very apreciated!!!! To summarize my problem: I want to export a huge database, and then start a macro on it which will select the data according to country and put it on different worksheets per country. However, the way the database is being exported out of Siebel Analytic, it is impossible for me to filter the countries as I THINK, the column in which the country is stated has been merged. More indepth: For my work I want to create a model where I...

When typing a formula, I cannot see the result. I have tried ctrl + ~, however this just expands all columns. Thi only seems to happen on some of my worksheets. Any suggestions are grealtly appreciate. I have also tried searchin for an similar question, but could not find one. Thanks!!! Shan -- Message posted from http://www.ExcelForum.com Shane Maybe the cells were formatted as text when you typed the formula? Andy. "Shane >" <<Shane.zxdn9@excelforum-nospam.com> wrote in message news:Shane.zxdn9@excelforum-nospam.com... > When typing a formula, I canno...

Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Is it possible to use the using statement with XmlTextReader? I tryed to use it, but it gives me the error message: Cannot implicitly convert type 'System.Xml.XmlTextReader' to 'System.IDisposable' Is there something I am doing wrong? // This no worky using(XmlTextReader xtr = new XmlTextReader("C:\\myfile.xml")) { // Do something } No. You're not doing anything wrong. You can only use the using statement with classes that implement the IDisposable interface. The XmlTextWriter doesn't implement that interface, hence the error message. The work...

In column "A" I have about 400 file names with each path, for example: D:\Folio_02\AL605_02.xls Each file contains a value named "ATT" and another named "XBB". I need to get these values in adjoining columns. I've tried every variation of the following I can think of without success: A1&"!"&ATT None have worked. I'm sure I have done this before but can't remember and can't find any of my old files with an example. Please help. Jim More like: A1&"!ATT" ************ Anne Troy www.OfficeArticles.com "jim...

Dear Excel 2003 users, I have a user in the field that likes to start formulas with a plus sign (becasue she uses a numeric keypad without an equal sign). Sometimes the formulas work, sometimes they do not. Here is an example: she enters +150/2 she could get: =150/2 or 75 Why does Excel sometimes run an immediate result on the entry and sometimes convert it to a proper formula (which is the desired result)? Thanks! Kevin Any chance she's hitting F9 after entering the formula (but before the enter key)? Or is there any chance she has a helpful macro that's jumping in where i...

Does anyone know how to format all the cells in a column to convert everything to UPPER CASE or Proper Case, regardless of how text is entered? I've set up a spreadsheet for storing names and addresses. I want to be able to type in lower case and have the names converted to proper case ("John Doe") and suburbs converted to upper case "(WAIKIKI") without having to mess around with the Shift or Caps Lock keys. Peter The following code placed in the worksheet code module (Right click on sheet tab and select 'View code...') will look at any entry in column...

Ever since I have ran the GDI Plus scanner tool (I had already installed XP SP2) Money 03 will not download my statements properly. I can save them to my computer, but previously I had been opening them when queried, and the updates were then automatically made to my file. But now nothing happens - including not getting the "your file has successfully been updated" message. Can this be fixed? Is there some new security setting? OR, can someone give me the instructions for saving the download, and then getting Money to update my file from that. hanks. In microsoft.public...