Hi In a similar vein how do I SUM conditionally, I've tried SUM(IF(... and now I am being driven nuts by the conditional sum wizard. Here is the info... sheet of dates with payments I want quarterly totals (my start date is 1-June not 1-Jan) Thanks Ian "Domenic" <Domenic.1f5awn@excelforum-nospam.com> wrote in message news:Domenic.1f5awn@excelforum-nospam.com... > > =COUNTIF(A1:A100,">200") > > Hope this helps! > > > -- > Domenic > ------------------------------------------------------------------------ > Domenic's Profile: > http://www.excelforum.com/member.php?action=getinfo&userid=10785 > View this thread: http://www.excelforum.com/showthread.php?threadid=274833 >

0 |

11/3/2004 2:31:54 PM

I was using constants in the places you have C1 and D1 and getting a real mess. I'll try this way. Thanks Ian "Alex Delamain" <Alex.Delamain.1f5m0t@excelforum-nospam.com> wrote in message news:Alex.Delamain.1f5m0t@excelforum-nospam.com... > > Column A has dates, Column B has payments > > C1 = start date, D1=end date > > =sumproduct((a1:a100>=C1)*(a1:a100<=D1)*(B1:B100)) > > This will total payments between start and end date > > > -- > Alex Delamain > ------------------------------------------------------------------------ > Alex Delamain's Profile: > http://www.excelforum.com/member.php?action=getinfo&userid=11273 > View this thread: http://www.excelforum.com/showthread.php?threadid=274833 >

0 |

11/3/2004 6:25:56 PM

Formula in C3 is =IF(E3>D3/12,E3,D3/12). I would like to have the cell (d3 or e3) where the data entered last control the answer in c3. -- Val Put this in C3 instead..... =IF(COUNT(D3:E3)=2,IF(E3>D3/12,E3,D3/12),"") Vaya con Dios, Chuck, CABGx3 "Val" <Val@discussions.microsoft.com> wrote in message news:DEC352A2-AF08-4796-BF77-E5C1F3F02E3A@microsoft.com... > Formula in C3 is =IF(E3>D3/12,E3,D3/12). > > I would like to have the cell (d3 or e3) where the data entered last control > the answer in c3. > -- > Val ...

Please, I need help with these formulas . . . 1.) if worksheetC!C12 says "1" then I want worksheet D!E18 to say "1" , but if C!C12 says "2" then I want D!F18 to say "2" 2.) if worksheet G!C12 says "G", thenD!I31 should say "G", but if G!C12 says "F", then I want D!H31 to say "F" 3.) if A!C12 says "930-12" and A!D12 says either "U" or "L" then D!E2 should say "U" or "L", but if A!C12 says "12-230" & A!D12 says "U" or "L" then D!F2...

Hi all, I am preparing an exam marksheet. In one cell, I add in this formula to check if a student pass or fail the whole exam. Condition ... the students must not get any "E" to pass the whole exam. =IF(OR(E7="E",G7="E",I7="E",K7="E",M7="E",O7="E",Q7="E",S7="E",U7="E",W7="E" ,Y7 ="E",AA7="E",AC7="E",AE7="E"),"Fail","Pass")) Now I want to change to another condition. The students must not get any "E", or "D&...

I am looking for a formula that will read (1/2 H) this for a register that i am building and i need to read if there is a half days holiday as i have one that reads if there is a holiday witch is =countif(k8:k17,"H") i need this to link in with that formula so it count up half days holidays would be greatful if there is anyone that could help... Cheers Hi You could try: =countif(k8:k17,"H") + (COUNTIF(K8:K17,"1/2 H")/2) Hope this helps. Andy. "John" <John@discussions.microsoft.com> wrote in message news:CB370206-4D51-4B74-B01F-8436FA8BE0B...

Starting a new financial year I want to clear all last year's data from a table, but leave the formulas and formatting. Any suggestions? Jeff Hi Jeff, Edit>Go To>Special. Check Constants and Numbers and/or Text Edit>Clear>Contents Make sure you have a copy of the file available! -- Kind regards, Niek Otten Microsoft MVP - Excel "Jeff Granger" <jeff.granger@btclick.com> wrote in message news:YsWdnV-Z_frGKBXbRVnyhwA@bt.com... | Starting a new financial year I want to clear all last year's data from a | table, but leave the formulas and formatting....

Hi community, for worksheets with a lot of columns and therefore a lot of AutoFilters possible, is there a way to display the resulting formula being effective in filtering in the status bar? Cheers Michael Hi, Do you mean can you display the criteria? There is nothing built-in to Excel to allow you to do this. It might be possible with VBA but I'm not sure. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michael.Tarnowski" wrote: > Hi community, > for worksheets with a lot of columns and therefore a lot of > AutoFilters possible, is ...

Posting this a 2nd time as the original post did not appear ========================================== Hi to All, For example, cell A1 contains a text string such as 56,58,12,1,22,893 I want to determine how many numbers are contained within the string Simply, counting the commas and adding 1 one seems to be the easy answer. . . How can I create an Excel formula to count the commas, or please suggest an alternative method. Thanks for your help, Cheers, RonW It did appear, but if you are trying to read it on Google Groups there seems to be about a one-day delay currently before posts ar...

In Excel 2002, if you create a formula that uses a range of data, i.e., sum(a1:e1), when you move the data in that range to another location, the formula changes to the new location of the data range. In other words, if cell f1 contains the formula sum(a1:e1), and I move the contents of a1:e1 to a4:e4, the formula in cell f1 changes to sum(a4:e4). Is there some way to make the formula remain sum(a1:e1), even when the data is moved? =SUM(INDIRECT("A1:E1")) -- HTH RP (remove nothere from the email address if mailing direct) "Alida Andrews" <Alida Andrews@disc...

IS is possible to use an arrange formula as a series when constructing an Excel chart. For example, in the Chart Wizard, on the series tab, add a series and enter an array formula in the values box? mrice256 <mrice256@discussions.microsoft.com> said: >IS is possible to use an arrange formula as a series when constructing an >Excel chart. For example, in the Chart Wizard, on the series tab, add a >series and enter an array formula in the values box? No. However, you can get the same effect by using an array formula as a named range, then using the named range as a series in...

Hi, I've got numbers manually entered in cells, i.e. =65+15+10. I need to count the number of "numbers" in each cell. The result fo the above would be "3". Otherwise, I have to manually look at each cel and type the number of numbers I see. Thank you, Alava -- Message posted from http://www.ExcelForum.com Hi this would be only possible using VBA. e.g. try the following: Public Function Get_Formula(rng as range) Get_Formula = rng.formula end Function Now use the following formula =LEN(GET_FORMULA(A1))-LEN(SUBSTITUTE(GET_FORMULA(A1),"+","")...

Often, in Microsoft Excel 2003, when I put a formula in a cell th results do not show in the cell. For example, today it was a simpl +b6-b7; Each of these cells had numbers in them - 15,000 and 10,00 respectively, yet the cell showed zero. Automatic calc was on. tried that same formula in several cells and they all showed zero. Ca anyone help -- Abra ----------------------------------------------------------------------- Abram's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1524 View this thread: http://www.excelforum.com/showthread.php?threadid=26871 Pe...

I'm no Excel whizz as you may be able to tell from this note. But if anyone can help I'd appreciate it. I have 2 columns of data repeated across 5 worksheets (different departments etc, the same columns but different data). What I'm trying to do is create a summary worksheet showing the count of entries across all worksheets where Column1=x and Column2=y. I've tried COUNTIF but it didn;t seem to work. Any suggestions on how to do this ? Is Pivottable the right choice ? Or should I use Access ? Regards Dave Hi one way: 1. Download the free addin Morefunc.xll (http://longre.f...

I'm new to excel and have a problem that I need solved. Can someon help? This is what I was givin. (L1*L2-L3/L4+7L5^2) L1=5, L2=2, L3=6, L4=3, L5=3 DECIMAL POINT AT 0. I came out with an answer of 8. Not really sure if I did it right o not? Thank You to anyone that replies -- Steve ----------------------------------------------------------------------- Steve S's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1468 View this thread: http://www.excelforum.com/showthread.php?threadid=26308 (L1*L2-L3/L4+7L5^2) has an error. Probably should be (L1*L2-L3/L...

Hello. Column A has dates. The format is 2010-04-29. There are multiple rows with the same date. Column B has numbers Please, provide me the formula IF: column A="2010-04-29" then SUM(B:B) Thank you. =IF(A1=DATE(2010,4,29),SUM(B:B),0) -- Regards Dave Hawley www.ozgrid.com "ma" <ma@discussions.microsoft.com> wrote in message news:77545BE3-1CAC-48BF-B407-16F0A9F2F883@microsoft.com... > Hello. > > Column A has dates. The format is 2010-04-29. There are multiple rows with > the same date. > > Column B has numbers ...

Hi, I have a weekly weekly data set that is the basis for a trend graph for one year. This works ok until about the 16th week, then I get "Series formula is too long". It seems the use of named range is the only way around this...? I have tried this, looked through previous posts and replies here, but this does not work for me. It seems only applicable to very simple data sets and graphs. The data set is as follows: There are two rows of headers (X) - the first is the week's number, the row below has the value of the week - for instance "Incoming&quo...

Hi! I have a work sheet that is linked to a system that I cannot access. To bring a value, the formula is the following: =mxdb|quotes!dvn.last that brings a result for the value or item "dvn" If I want to change that value "dvn" for another with a different name, lets say "pep", is there a way where I can write the new values in a column and then the formula inserts the new value that I need? So the formula would look like: =mxdb|quotes!pep.last (so I get the "pep" result) What I am doing now, is change the name in the formula for each cell tha...

Help! We have a date in the format 01/01/2000 in column A1. We need to produce a date in column B1 that is five years on minus one day (i.e. 31/12/2005). Does anyone know of a way to do this? Many thanks! The date would be 31/12/2004, not 2005. =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)-1) HTH Jason Atlanta, GA >-----Original Message----- >Help! We have a date in the format 01/01/2000 in column A1. We need to >produce a date in column B1 that is five years on minus one day (i.e. >31/12/2005). Does anyone know of a way to do this? > >Many thanks! >. > Works per...

I have the following data: gift_id suffix gift_desc size BX02 Chateauneuf - Semi Dry White 750 ml (12) BX07 Cabernet Sauvignon (VDP) 750 ML (12) BX08 Valrose (VDP) 750 ML (12) CH02 NYS White Sparkling 187 ML (24) CH02 NYS White Sparkling 750 ml (12) I would like to return a value in the "suffix" column based on the text in the "size" field. for example =IF(ISNUMBER(SEARCH("750",D2)),-750). I want -750 to appear in the suffix column if partial text "750" appears in the size column and similarly I want ...

I am new to Excel 2007 and miss the ability to click the = that used to be in the box at the left of the formula bar. It would put the beginning = in so that a formula could be entered directly by clicking on the first cell, entering an operator and clicking the second cell. That was very efficient. I suspect that it still exists, I just can't find it. Thanks for any help. That with XL2003; next to the Formula Bar is fx which when press inserts an = and opens the Insert Function dialog. The same in XL 2007. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/...

Is there any way I can have the OS report back the actual number of CPUs installed in an NT machine, rather than the doubled up count as per chips with Hyper Threading (P4 and Xeon chips). Interrogating the registry we get the number of CPUs that appear to the OS... I need to be able to count the physical number. Any help appreciated. Nigel Look at the documentation for GetLogicalProcessors() and follow the link to SYSTEM_LOGICAL_PROCESSOR_INFORMATION. From what I can tell all the logical processors on the same physical processor should be returned in the same SYSTEM_LOGICAL_PROCESSO...

Have a worksheet been using; don't have a blank master. Now i need a blank worksheet with the formulas still intact. doodah, try this, Edit, go to, special, constants, OK, delete -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "doodah" <doodah@discussions.microsoft.com> wrote in message news:938DD5AB-A704-4C9C-93EE-772ADA2BD872@microsoft.com... > Have a worksheet been using; don't have a blank master. Now i...

In my old version of office, I could start writing a basic formula and mid-way scroll to another location on the worksheet to select a cell for the formula. I cannot find any way to do this in 2007 and have to use the scroll bar at the side of the worksheet - am I missing something (apart from the tooks menu!!) (Has anyone retroengineered a way to get rid of these thrice damned ribbons and get the old office back?) Any help appreciated! Thanks Click the Office Ribbon Button (colorful round circle at the top Left corner)>Excel Options>Advanced>Editing Options>C...

I have a survey that I want to graph the answers to in a pie chart. However the particpates could choose more than one response to the questions. These means that each pie piece must be a percentage of the total surveyed not of the total answers. I don't know if this is even possible. If you know how to do this please let me know. I am having a hard time understanding what you want to do and how you want the data reported. How about an example that illustrates what you want? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Off...

Running a worksheet that tracks all household expenses for the year. First page is Home that contains totals from other pages named Insurance,Medical, utilities. How do I place a total say from cell k5 on the utilities pages onto the Home page at f5? I have the formulas set up that track and compile the sums. Running Excel 97 SR-1. Thanks, Steve T In Cell F5: =Utilities!K5 Assuming your Utilities sheet is name "Utilities" Thanks Linc, that was easy enough. Steve T "Linc" <mlincoln@earthlink.net> wrote in message news:1137100852.386326.206400@z14g2000cwz.g...

Can anybody help with the following, I have table numbers (1 - 10) in Col A and a menu choice (Text) in Col C. I need to calculate the total of the menu choice for each table. I have used =COUNTIF(C5:C100,"Prawn Cocktail")which gives me a total for all ten tables, but I needing to calculate a total for each table. Thanks in anticipation. Mike I know that there is a way to lookup with two conditions, but i can tell you, the easiest way to do the same would be pivot table. You can update the data on one worksheet and create pivot on the other. When you want the updated data b...