array formula problem

Excel array formulas drive me crazy. I think part of my brain formed early
under the influence of Fortran (yes I know that dates me).

I have two columns of data named 'Date_In' and 'Date_Out. I want to count
all of those rows in which 'Date_In' is before a certain date ('Then') and
'Date_Out' as after another date ('Now'). Why doesn't this work?

{=Sum(if(and(Date_In<Then,Date_Out>Now),1,0))}

Richard Yates



0
rayates53 (2)
9/30/2005 11:28:25 AM
excel 39879 articles. 2 followers. Follow

2 Replies
530 Views

Similar Articles

[PageSpeed] 19

What happens when you tried it?

Do Date_In and Date_out refer to whole columns (array formulas don't like whole
columns).

Are now and then named values?  I'd stay away from use now as a name--it looks
too close to the built in =Now() function.

=sumproduct(--(a1:a10<date(2005,9,1)),--(b1:b10>date(2005,9,30)))
or
=sumproduct(--(a1:a10<date(2005,9,1)),--(b1:b10>today()))

maybe another way to accomplish the same thing.

=sumproduct() likes to work with numbers.  -- changes trues and falses to
+1/0's.




Richard Yates wrote:
> 
> Excel array formulas drive me crazy. I think part of my brain formed early
> under the influence of Fortran (yes I know that dates me).
> 
> I have two columns of data named 'Date_In' and 'Date_Out. I want to count
> all of those rows in which 'Date_In' is before a certain date ('Then') and
> 'Date_Out' as after another date ('Now'). Why doesn't this work?
> 
> {=Sum(if(and(Date_In<Then,Date_Out>Now),1,0))}
> 
> Richard Yates

-- 

Dave Peterson
0
petersod (12004)
9/30/2005 12:23:09 PM
Thanks, Dave. sumproduct worked like a charm (after I used a couple of other
magic spells).

>  (array formulas don't like whole columns).<

This helped, too. And along the way I discovered that array formulas also do
not like AND(array1,array2). In fact it just does not work at all.

Richard


0
rayates53 (2)
9/30/2005 11:37:06 PM
Reply:

Similar Artilces:

"Array Index Out of Bounds"
I recently purchased "Office Professional Ed 2003." With it came "Business Contact Manager." When I double click to open a particular contact or business contact, I get the above "subject" message. Is there a reason, fix, or solution for this? Thanks, John ...

Problem with Sales for Outlook
I have CRM 1.2 installed and if fuction properly from server and from clients, if you go from web. I've installed on clients the Outlook/Sales, but on Outlook folders, inside Microsoft CRM folder, I see only Custom Forms and other 2 folders, if I go on root folder, Microsoft CRM, nothing happens. I've tryed to uninstall and reinstall a lot of times Outlook/Sales, removing SQL folder and MSCRM.pst following the manual, but nothing. Help me! Claudio Claudio, Have you tried disabling and re-enabling the crmaddin.dll file on the client. You do this in Outlook by going to Tools ...

formula Memory
Hello, Those who like to store complex formulae for future use, can install my Add-In: 'Formula Memory'. This Add-In adds a command to the Help menu, allowing to save or reenter those formulas by just a few clicks of the mouse. Also check my sheets selector. http://users.skynet.be/onderland/Excel.htm 18- SheetsSelector 19- FormuleMemory Luc ...

Integration Manager problems after 8.0 upgrade
Just upgraded from v7.5 to v8.0; running same integrations as before upgrade; we are losing bits of data in the integrations, although no errors are flagged. e.g. on Purchase Order integration, the line number (pop10110.linenumber) field is not getting set; all line numbers are 0 (zero); also, on SOP integrations, the freight amount and trade discount amount are not appearing; all of these integrations have worked for an extended period before the upgrade. Ideas? We figured this out; apply SP3 of Integration Manager 8.0 and update destination adapters to the 8.0 adapters fixed the...

no formula calculation
In the worksheet no formulas are calculated anymore. When I put the following in a cell: =A1+B1 This text is displayed, no calculation is made. Even when I put the following in, =1+3 no calculation is made. Everything is displayed as is. What is the problem?? Is the cell formatted as General (or some number)? If it's formatted as Text, change it to General, then select the cell and hit F2. Then Enter. You don't have a space character before the leading equal sign? And you don't have tools|options|view tab|formulas checked, do you? Junior Trimon wrote: > > In th...

Recovery problems after installing KB979970 (2007 QFE rollup)
Hello, since we have installed the 2007 QFE rollup for DPM 2007 we have the following issue. When you look in DPM 2007 at the restore tab, en you want to restore a file from a protected server, we cannot select the current day. This problem only occurs when recovering files. When we want to restore a DB, DPM does show current day. and recovery is not a problem. I have looked at the settings in de protection group, but the settings are correct. (the same as before, when it did work) Has anyone seen the same problem? Thank you! ...

Nesting formulas
How do I nest these formulas into one cell: =IF(MID(D6,4,1)="-", IF(M6="J",TEXT(0,"0000000"),TEXT("0"&LEFT(D6,2)&MID (D6,4,4),"0000000") IF(M6="G","XXXXXX","yyyyy") IF(M6="U",TEXT(0,"0000000"),TEXT(0,"0000000") ...

additem to combobox with an array
hi, i'm trying to add itemns to an multicolumn combobox (3 columns) using an array. if i just try to add 1 column there is no problems, but when i try t add more columns to the combobox it wont work anymore. tips anyone? tia jock -- jock ----------------------------------------------------------------------- jocke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=76 View this thread: http://www.excelforum.com/showthread.php?threadid=47167 Did you change the .columncount property? I made a small userform and picked up some values off a worksheet to create...

Need a FORMULA to round off
I have a question, quite simple but complicated for a beginner like me. Say I have a figure .... 16 in CELL A1 and in C1 5.05263158 C2 6.73684211 C3 4.21052632 but wanted the numbers to be rounded to the nearest Hundredth totaling it to be the number A1? How can I do that? I mean if I just do the math it should look like this C1 5.05 C2 6.75 C3 4.20 Please help and thank you in advance. Hi try =ROUND(value,2) -- Regards Frank Kabel Frankfurt, Germany Koji wrote: > I have a question, quite simple but complicated for a > beginner like me. > > Say I have a figure .... ...

Hibernation problem
Using SP3 with all updates on a desktop, if I resume after hibernation but do not click my logo on the login screen within a minute or so the computer shuts down again. How can I stop it shuting down unless I tell it to? It is really anoying to get a phone call just after restarting the computer and then finding that when the call is over the computer is not up and running unless I remember half way through the conversation to go and click the startup logo. All my power option are set to never power down uhnless I click a button to do so. On May 16, 3:07=A0pm, "L...

Problem updating dynamic table, from csv file
I have created a dynamic table in excel that gets the data from a cs file. I copied the xls file to several computers (and also de cs file), but if I try to update the data in the dynamic table it jus works in some of the computers, while others just show "ODBC text, to few parameters". The csv file is "comma separated", so I checked (and changed if needed the list separator value in Control Panel, but the problem persists i some of the computers. What's wrong? thanks in advanc -- gjoseval9 ----------------------------------------------------------------------- g...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

IF problems, PLEASE HELP!!! #3
Actually that will work fine, no reason to mess with the IF function -- ryangruh ----------------------------------------------------------------------- ryangruhn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1603 View this thread: http://www.excelforum.com/showthread.php?threadid=27497 It can be done quite easily with the IF function but it doesn't scale very well, so if you had more than 7 IFs you would crash, but CHOOSE and MATCH will allow many more arguments than that. -- Regards Ken....................... Microsoft MVP - Excel ...

Array+Array
Hi, I have 2 (or more) arrays, how can i combine them into 1? example: Array 1 = "1, 2, 3, 4" Array 2 = "A, B, C, D, E, F" Result: Array_Combined = "1, 2, 3, 4, A, B, C, D, E, F" br, Danny On Jan 7, 9:23=A0am, Danny <dannypct...@gmail.com> wrote: > Hi, > > I have 2 (or more) arrays, how can i combine them into 1? > > example: > Array 1 =3D "1, 2, 3, 4" > Array 2 =3D "A, B, C, D, E, F" > > Result: > Array_Combined =3D "1, 2, 3, 4, A, B, C, D, E, F" > > br, >...

array
Dear programmer I don't understand the array can any one give very easy example to understand the types of array ( I can use command button and text box) Example 'Don't forget to write notes for me ' the next line will define the array. Dim a(2) As String ' the next lines define the data of array a(0) = "1" a(1) = "2" a(2) = "3" And so on Notes: The array is very difficult please give me easy example "a" <A@a.com> wrote in message news:uy9smdVUIHA.4476@TK2MSFTNGP06.phx.gbl... > Dear programmer I don't understa...

Excel Problem #13
My rows and columbs are both designated by numbers. ( Cell 1,1 instead of cell A,1 ) Why is this and how do I correct it? Hi J, Tools | Options | General Tab | Deselect R1C1 reference style --- Regards, Norman "jsylvester3" <jsylvester3@discussions.microsoft.com> wrote in message news:FCBFBF9B-07A0-43C8-BB5E-409639733A4C@microsoft.com... > My rows and columbs are both designated by numbers. ( Cell 1,1 instead of > cell A,1 ) Why is this and how do I correct it? ...

Searching problem
I was trying to search for an emailed receipt for a mouse I had purchased and discovered that there are no records past about a year ago. Apparently everything past then was archived. How can I search archives? I opened the archive folder and there is nothing in there past a year either. If I use the File / Open command I can see past Outlook data files from 2006 and some backup files from earlier times also. I am leery of opening one of those files because I don't want to lose my current mail. How can I safely view these old files to search them? You just open them. Eaxactly ho...

how do I create an array
I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3:G11}),""))) please help ...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Outlook problem #17
I had to un-install Office 2003 beta today and re-install Office XP. And having major problems with Outlook. First it was complaining about some missing DLLs and MAPI32.DLL and now after multiple repairs and re-installs I got it to the point when it does not complain, however it does not send/receive mails either - error operation failed. Any help how to fix friendly app? Thx YuriW ...

Accessing the CCD array of the camera using MFC
Hello, I am capturing the images from the camera using VC++ MFC . And each image is of size 1280*1024. When I want to do processing over certain area, actually I want to divide the Image height(1024) into 4 blocks (256,256,256,256) keeping the width (1280) constant. So that I will get the whole image into 4 small images. Can anyone please kindly tell me How can I able to do this ? I am thinking of accessing CCD array, If accessing CCD array is a solution for this.. Please help Thanks in advance James First, you need to have a device driver that connects to the camera. This is not an MFC ...

Date formulas
Hi, I'm doing a timesheet. The month begin on the 21 and end on 20th of each month (don't ask). It'easy to set a listmenu to choose the month, have the first day to start with the 21, having 31 line with =1+precedente line. But for the three last line, idon't know how to have it to stop displaying dates after the 20th, for Febuary and the other 30 days months. An idea anyone? thank In A2: =IF(A1="","",IF(MONTH(A1+1)=MONTH(A1),A1+1,"")) Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel &q...

RPC problems on server
Hello, We have moved our exchange server 2003 SP2 to a new hardware. Since then some clients are having problems to connect to the server. They get a timeout and a message, outlook is trying to connect to the server. When we try again it works most of the times. We already did a deep analyze with health check, but no problems here. We already run the exchange performance analyzer and we have noticed two problems Some latency's on disk and high RPC for the users. So when we look in the logon folder on the exchange we see for each mailbox approx 8 - 15 logons. We already ...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Problem with OAB
I have problems with my Offline address book It seems like my Outlook downloads an OAB from our organisations Exchange 5.5 period. How comes that Outlook downloads such old information? MN What does your Exchange administrator say? "Valleyriver" <please@nospam.com> wrote in message news:OYYl3xuxGHA.3452@TK2MSFTNGP04.phx.gbl... >I have problems with my Offline address book > > It seems like my Outlook downloads an OAB from our organisations Exchange > 5.5 period. > How comes that Outlook downloads such old information? > > MN > > There was a ...