It works greats, thanks so much Bob

Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

hi, is there a function that does this if a1=1, if true a2=b1, if false a2 stays as it was (EITHER WITH A ZERO VALUE OR THE VALUE THAT MAY HAVE BEEN ENTER PREVIOUSLY FOR EXAMPLE B2= 10 A1= 0 B1=0 B1 WILL BE INCREMENTED BY A MACRO A1=10 B1=1 A1=10 B1=2 THOUGHT AN IF FUNCTION WITHOUT THE FALSE BIT WOULD WORK BUT IT DIDNT ALL IT GAVE ME WAS THE TEXT "FALSE" Hi Carlos1973! I'm afraid that you can't use functions to do other than return a value to a cell. Closest you'll get is: A2: =IF(A1=1,B1,0) or: A2: =IF(A1=1,B1,C1) You ...

If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

I'm looking for a function that will reference a cell then lookup that cell in a table and use the multiplier in the table. I'm not explaining very well so Im hoping that my example will help you understand: Column A b c Sector P&L w/o Admin P&L w/ Admin PC 517.9 PC 0.0 PC 0.0 TD 1,993.6 TD (2,508.0) TS 431.3 TS 355.7 Table reference: FD 1.20 PC 1.03 PR 1.52 SIJ 1.00 TD 1.32 TS 1.63 In column C I would like to put a formula that; if column A mactches column A in table reference th...

I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

We have a very serious issue where we wish to convert (change) the functional currency of a GP system that has been in production for a very long time => has a lot of data accumulated. As far as I know this can not be done by any GP functions/utilities. Does anyone know of any ways that we could go about doing this or any third party products or any leads at all that we could follow? Any help will be greatly appreciated. Thanks, Elias Hi Elias, Unfortunately, I guess you are right on GP not having an out-of-box solution for this. In the past on one occasion I had to conver...

I would like to find Macro or Function that will make text size to suite text Length? eg. if the first cell has "bill smith" then the text size would be 10. But if you had "Christina Vandermear" then the text size would be as small as 7 in order to fit in that same size cell. I know it's a bit of an odd request, but is there a way to do that? thanks Hi only possible with VBA. You may download the following addin for this extension of the conditional format (as Excel's build in format can't change the font size): http://www.xldynamic.com/source/xld.CF...

Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

Alright this one might seem a little odd. Basically when I initialize my dialog I have to call a routine (RunSameThread) which unfortunately calls some functions set with a library (which I don't have the source code for) which may not exit. (Basically this call should initialize the COM port and send's some commands to a transceiver attached to the port, then wait for a response before returning). Now, if the transceiver is not hooked up (or turned on) the COM port initialization will go though, but the library function will sit in an infinite loop :( Thus, I am trying to write a t...

Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

I'm trying to do a similair process to the lookup function, but the dat isn't sorted, and due to some cross sheet links, I am unable to sor the data. So I'm looking for a function to find the correct company i one range and then return the correct multiplier in the correspondin range... I will comment my function with missing code Function FindReturn(Company) Set Foundcell = ActiveSheet.Range("B62:B69").Find_ What:="end", LookIn:=xlValues, LookAt:=xlWhole) If Foundcell Is Nothing Then FindReturn = 1 Else r = Foundcell.Row ' FindReturn = value in cell wit...

I often use excel as a fancy calculator. But often I forget to start the calculator with the = sign. So if I want to add 44 + 66, I forget to type in an = sign first, an it gives me an error message. Is there a setting to turn that off, so I can type in 44 + 66 and it gives me an answer. I always have Excel running and it's also my calculator. I never use use a calculator anymore! The answer to your question is no. The equal sign tells Excel that you're entering a formula that needs to be calculated. You can use a plus sign (+) instead of the equal sign (=) if you alwa...

I am brand new to using the charting feature of Exel and am having trouble with what should be a simple operation. I want to create a graph of y=f(x) -- say for simplicity's sake f(x) = x^2. I have in column A, for x, the numbers -10 through +10 and in column B =A^2. Column B shows the correct values but have not been able to create the pretty graph I know so well. Would someone give me a bit of help here? Thank you very much. Where do you get stuck? Select the data range, click the Chart Wizard. In step 1 select the XY Scatter chart. That should give you what you want. ...

I used to be able to have messages in my Outbox sit there till I hit send/receive by checking "disable send/receive" on the tools menu. For some reason, disabling send/receive now only disables the automatic receive function. My emails in my Outbox are sent automatically, as soon as they hit the outbox. I send monthly emails to my contacts thru the mail merge program in Word. I then go to my Outbox to attach a document to them. It used to be that if I disabled the automatic send/receive function in the tools menu, the messages in my outbox would sit there so I could add ...

Hi I have a range A1:S1 with a series of integers and a range A2:S2 with another series of integers. How can I count all the instances where the equivalent cell in the second range is equal to two less than that in the first range. eg 6 4 9 7 8 etc 4 3 7 8 7 etc the above would count 2 - Columns A and C Thanks in advance Sandy =SUMPRODUCT(--(A1:S1-A2:S2=2)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/...

Does anyone know if a VBA Function can be used to execute (call) a Macro? How would you do this? A VBA function IS a macro. What exactly are you trying to do? On Tue, 23 Nov 2004 06:11:02 -0800, RKGriffin <RKGriffin@discussions.microsoft.com> wrote: >Does anyone know if a VBA Function can be used to execute (call) a Macro? How >would you do this? I am using the "Function," below, which appears to be different than a "Sub" Routine type Macro, because in a sub-routine I can call another sub-routine and in this thing, I can't. Actually, all I want thi...

Hi. I need some help. I have two columns in a sheet, A1:A100 and B1:B100, in which it is: The first column A1:A100, have some data (numbers). In the second column apply B1=A1, B2=A2,……..B100=A100. In the B101 i am trying to apply the SUM(B1:B100), but only if the cells A1:A100 are not empty (the zero value must be considered as number, not empty cell). In the case, which the cells A1:A100 are empty, i would like to not print anything in B101 cell. I tried the use of ISBLANK() function {IF(ISBLANK(B1:B100)=FALSE;SUM(B1:B100);"")}, but when the A1:A100 cells are empty then th...

I get e-mails from this Radio Guanaca several times a day. I click on Message, Block Sender, but e-mail keeps coming in, and it's the exact same e-mail address/domain, whatever every time. Why isn't this sender being blocked? Thanks! no more radio suezeee ...

I have this formula in use and it works perfectly. I now need to change the sum area from H4:h484 to H4:J484 and now it only give me 0. How do I get it to sum the larger area?? =IF($B3<=0,"",SUMPRODUCT((SVEexp!$B$4:$B$484=Summary!$B3)*(SVEexp!$C$4:$C$48 4=Summary!$C3),SVEexp!$H$4:$H$484)) Thank you, Jerry Hi You need to make sure that all of the ranges in SUMPRODUCT are the same size. If you alter one of the ranges, you'll have to alter the others accordingly. Hope this helps. Andy. "Jerry Kinder" <jkinder@jkinder.com> wrote in message news:eeznF54...

I have a Windows 2003 Forest with a Forest Root Domain, and 3 child domains. Can I upgrade the forest root to windows 2008 functional level but leave the other domains at 2003? What do I need to do with the Forest functional level? I would like to take advantage of the enhanced features of DFS on W2k8 R2 but I need the domain functional level to be upgraded. CK wrote: > I have a Windows 2003 Forest with a Forest Root Domain, and 3 child > domains. Can I upgrade the forest root to windows 2008 functional > level but leave the other domains at 2003? What do I need to do wit...

I have a serious issue that I can not seem to resolve. I wish I could post the actual spreadsheet as it is hard to explain....but here is my best attempt I need to recreate this summary page Jun Jul Aug Sep First Order 51 18 19 9 First Re-Order 0 34 20 11 Total Ordering 51 52 64 55 Store Front Distribution10.2% 10.4% 12.9% 11.0% Total Depletions 106 73 198 92 Average Case Per Store2.1 1.4 3.1 1.7 My data is as follows: (account names and order history by month...see below). The total depletions, and average deps are not the problem....that is easy....

I have 3 columns of data, NAME, DATE,VALUE NAME Date Text Carlos 1/1/4 10 Carlos 2/1/4 20 Carlos 5/6/4 30 Carlos 6/6/4 40 Peter 5/5/4 50 Peter 20/6/4 60 The forumla that I need to use is Look for CARLOS in the database, and retrieve VALUE when NAME is CARLO and DATE is the LATEST (for carlos of course). In this example if I look for Carlos the data I want is 40 If I look for Peter the data I want should be 60 The formula I tried was =sumproduct(NAMES=Carlos)*(DATES=MAX(DATES)),VALUE) I think it is because it doesn't match the latest date for carlos. ...

Hi folks, I have a form called tbldocinput that contains a subform countrack. The form countrack is behind a query also called countrack that will provide me with the MAX() of the value of a tracking number that I am trying to create and autogenerate. I want the user to input the data on the tbldocinput form which is essentially a data entry form. When the user clicks save, the countrack is requery as well as a unbound field on the form tbldocinput that is default to 0. If there is a value in the countrack form field dtrack, then I want to use that value to input in my tracking nu...