#### sumif using the now() function

```I am trying to sum numbers using the NOW() function as my criteria, however
no luck.

sumif(\$D\$1:\$BC\$1. ">=NOW()", D2:BC2)  Any help would be appreciated.

Thank you

```
 0
Haines (1)
3/31/2006 8:28:03 PM
excel.newusers 15348 articles. 2 followers.

6 Replies
559 Views

Similar Articles

[PageSpeed] 16

```Try this:

Assuming you try to sum D2 to BC2 on your example.

=if(\$D\$1:\$BC\$1>=Now(),sum(D2:BC2),""

--
renega
-----------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1045

```
 0
3/31/2006 8:44:19 PM
```renegan: Thanks for your response, however I got #VALUE! in the cell. Any
suggestions

"renegan" wrote:

>
> Try this:
>
> Assuming you try to sum D2 to BC2 on your example.
>
> =if(\$D\$1:\$BC\$1>=Now(),sum(D2:BC2),"")
>
>
> --
> renegan
> ------------------------------------------------------------------------
> renegan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10450
>
>
```
 0
3/31/2006 9:20:01 PM
```Here's another variation to try:

=SUM(IF(\$D\$1:\$BC\$1>=Now(),D2:BC2,0))

This is an array formula, so when you have typed it in (and if you
subsequently edit it) use CTRL-SHIFT-ENTER rather than just ENTER - if
you do this correctly then Excel will wrap curly braces { } around the
formula. You should not type these yourself.

You can copy the formula down if you wish.

Hope this helps.

Pete

```
 0
pashurst (2576)
3/31/2006 9:30:33 PM
```Are you comparing dates and times or just times? If dates and times

=SUMIF(D1:BC1,">="&NOW(),D2:BC2)

with times only

=SUMIF(D1:BC1,">="&MOD(NOW(),1),D2:BC2)

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

"Gene Haines" <GeneHaines@discussions.microsoft.com> wrote in message
news:E23C69D8-8718-4318-A47F-E4E7BC5DED6E@microsoft.com...
> renegan: Thanks for your response, however I got #VALUE! in the cell. Any
> suggestions
>
> "renegan" wrote:
>
>>
>> Try this:
>>
>> Assuming you try to sum D2 to BC2 on your example.
>>
>> =if(\$D\$1:\$BC\$1>=Now(),sum(D2:BC2),"")
>>
>>
>> --
>> renegan
>> ------------------------------------------------------------------------
>> renegan's Profile:
>> http://www.excelforum.com/member.php?action=getinfo&userid=10450
>>
>>

```
 0
Peo
3/31/2006 9:38:54 PM
```Ok:

\$D\$1:\$BC\$1>=Now() doesn't work. 2 things you can do:

1- You can use AND(\$D\$1>=Now,\$E\$1>=Now,..........,\$BC\$1>=Now) which
won't be pretty
2- Create another row under the row you check the data, check each cell
condition one by one with an if statement and get 1 if true like:
D2=If(\$D\$1>=Now,1,0)
Add all the cells with 1 and 0s. If total is less than the number of
columns between D and BC, then you don't add, if it does you do the
sum. Like:
if(Sum(D2:BC2)=52,sum(D2:BC2),"")

--
renegan
------------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10450

```
 0
3/31/2006 9:40:36 PM
```Pete, Peo. Thank you very much..... both formulas worked. U2 renegan 4 ur time

Regards
Gene Haines
"Peo Sjoblom" wrote:

> Are you comparing dates and times or just times? If dates and times
>
> =SUMIF(D1:BC1,">="&NOW(),D2:BC2)
>
> with times only
>
>
> =SUMIF(D1:BC1,">="&MOD(NOW(),1),D2:BC2)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Gene Haines" <GeneHaines@discussions.microsoft.com> wrote in message
> news:E23C69D8-8718-4318-A47F-E4E7BC5DED6E@microsoft.com...
> > renegan: Thanks for your response, however I got #VALUE! in the cell. Any
> > suggestions
> >
> > "renegan" wrote:
> >
> >>
> >> Try this:
> >>
> >> Assuming you try to sum D2 to BC2 on your example.
> >>
> >> =if(\$D\$1:\$BC\$1>=Now(),sum(D2:BC2),"")
> >>
> >>
> >> --
> >> renegan
> >> ------------------------------------------------------------------------
> >> renegan's Profile:
> >> http://www.excelforum.com/member.php?action=getinfo&userid=10450
> >>
> >>
>
>
>
```
 0
3/31/2006 9:49:12 PM

Similar Artilces:

How to erase a line drawn using line bar next to + = bar
I drew a line to separate text but now cannot get rid ot it. When I edit or move the text the line keeps reappearing even if the text is cut and pasted it moves with it, or stays with what it left See http://word.mvps.org/faqs/formatting/CantGetRidOfLine.htm. -- Stefan Blom Microsoft Word MVP "janetland" <janetland@discussions.microsoft.com> wrote in message news:0EAC6171-7D72-4664-A41F-A4D7388B213F@microsoft.com... >I drew a line to separate text but now cannot get rid ot it. When I edit or > move the text the line keeps reappearing even if the tex...

How to automatically add a worksheet using macro?
Can I add adding a worksheet in a macro? How do I do that? Ex. I create a new macro. One of the steps in my macro is to insert a new worksheet. Dim NewWks as worksheet set NewWks = worksheets.add with newwks .name = "Your New Name Here" .range("A1").value = "Hi there" End with eva wrote: > > Can I add adding a worksheet in a macro? How do I do that? > Ex. I create a new macro. One of the steps in my macro is to insert a new > worksheet. -- Dave Peterson Thank you "Dave Peterson" wrote: > Dim NewWks as worksheet > set Ne...

Time calculation with now()
I am trying to create a train schedule that compares the departing time of the train with the system time. I've tried subtracting the column containing the time a train departs from the now() column (which I thought read the system time) from but the times I receive do not make sense. Any suggestions would be greatly appreciated. Thank you. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ time is part of a day so try =(a1-b1)*24 "dfeder" <dfeder.u9...

UML: using existing classes/structures in a library
Hi, How can I create a UML diagram that uses existing classes in a library. For example, I want to build a UML static structure that has a class ProxyConfiguration. That class has a method that takes a SoapEnvelope as a parameter. SoapEnvelope is a class that is not part of my application, it is part of an existing library. So I don't want to add a class with the name SoapEnvelope just to allow me to make my diagram. Thanks, -- Sven > So I don't want to add a class with the name SoapEnvelope just to allow me > to make my diagram. That is EXACTLY what the Wrox book on ...

cashflow in your bank account NOW!!!!! #2
i think u should try these websites! ;) go to this url: a) http://offto.net/forexkiss1/ b) http://offto.net/5technic1/ c) http://offto.net/50weeksforex1/ d) http://offto.net/makemoneyfirst1/ e) http://offto.net/cashsecret1/ f) http://offto.net/millionairesecret1/ g) http://offto.net/luxuryhomes/ h) http://offto.net/forextrader1/ well, these are just my suggestions.. i dont get any commissions for promoting these websites to you. good luck! ;) ...

Autoclose Access Database if not using for more than a certain period
My Access database is working in a multi-user environment. Is there any function to close the access database if the user is not using the file for more than 2 hours? Thanks. See the following: http://support.microsoft.com/kb/210297/en-us or another option would be: http://support.microsoft.com/kb/304408 <xiaodan86@hotmail.com> wrote in message news:1178590698.082941.302910@e65g2000hsc.googlegroups.com... > My Access database is working in a multi-user environment. > Is there any function to close the access database if the user is not > using the file for more than 2 ho...

Contract Administration should not use the history exchange rate
Contract Administration shouldn't use the historic exchange rate for renewal and revenue recognition. It should use the actual exchange rate. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/e...

returning the value in Colunm C using Colunm A as Ref
Hi I am trying to format a spread sheet and need to return the value of colunm C but the problem is Colunm A is blank as it runs in sequence (see below) It looks like the following A B C 1, P100AA Info 300 2, P100AB Info 150 3, "Blank" "" 100 4, "Blank" "" 50 5, P100AC Info 300 How do i return the last figure for the item in Row 2 (colunm A) i.e. 50? If i do a logical test how do ...

I wanted to use Partition Magic to partition my hard drive, so I used the boot CD. When I loaded it up, it said that there were 2 errors in my hard drive and it could fix them, so I pressed fix. Unfortunately, I was running Vista and I only know now that they're incompatible. However, now my hard drive is completely screwed! If I plug my hard drive into my computer, my computer refuses to do anything after POST, it doesn't boot a CD or run anything from the hard drive. I unplugged my hard drive and it's booting CDs now, even though my CD was the primary boot device anyway...

I used Omsgclas.exe Utility and my contacts don't use then new form
I created a new form for contacts. I followed the three steps in the document How to Update Existing Items to Use a New Custom Form. I can click on Action and do new contact and the new form is used. I used Omsgclas.exe utility to change the message class of the contacts. It shows as changed. However, when I click on an old contact (previous to changing the form), A message form comes up (not the old contact form and not my new contact form). Anyone have any help how to get my new form pulled up for old messages? Note: I had a form with a ' in the name, I made a new form w...

Storing VBA Code In a Cell to Use real Time
I want to store multiple values in Column A and VBA Conditional Code in Column B. The Conditional Code is just text and is just stored on the worksheet. When a macro is run and a variable equals the value in column A then the code stored in Column B executes inside the macro. For example, A1 = "ALLEX" and B1 = "If Not Left(route,1) = "K" . So, when the macro runs and checks to see if "ALLEX" is in Column A. If so ,then it will use the associated Condition: If Not Left(route,1) = "K". directly in the Macro. So, if both condition...

SumProduct/SUMIF? #4
why the double hyphen? what does this do? when I take these out the formula it returns zeroes when I leave them in it works....? can someone explain this or give m a link to a web site that does, thanks -- kkondrat ----------------------------------------------------------------------- kkondrat1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=600 View this thread: http://www.excelforum.com/showthread.php?threadid=26650 Hi see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "kkondrat1" <kkondrat1.1...

Re-using a "Door Prize No"
Hi, I’m on Access 2003 on XP Pro with all the latest updates. I’m working for a charity that has a membership database. Each member is assigned a door prize number. The goal is to not have any gaps in the door prize number. When a member leaves the area, we flag that member as “gone” and I want to re-assign their door prize number to the next new member. I’ve already beat my head against the wall talking to management about why they reuse number and they are not going to budge. I can logically see what I need to do, but I don’t know how to code it. I would appreciat...

use forwarded email address on new emails
I have my emails forwarded to my ISP. I want all new out going emails to have the address of the account that forwards to the ISP. I don't want any one to see my ISP address. Is this possible with Outlook 07? Thanks navanax wrote: > I have my emails forwarded to my ISP. I want all new out going emails to > have the address of the account that forwards to the ISP. I don't want any > one to see my ISP address. Is this possible with Outlook 07? Thanks In the e-mail accounts that YOU define inside of Outlook, put whatever you want as your e-mail address. Ra...

Creating a calculated field using dates in a form
I am trying to use dates from 2 fields in a form (HireDate-DOB) to calculate age. Then, using the age, I want to display a message if the age is less than 18. I think I have written a conditional statement correctly, but am unsure where to place it so that the message displays when the condition is met. I am totally new to Access 2007 and VB. You did not say what the content of your 'conditional statement' was and how you plan on using it. What has HireDate to do with it? Is it you want to see if their age is less than 18 on the date of hiring? Then this will c...

SUMIF #6
I can use SUMIF with one range but I wish to calculate a sum based on relevant criterea in two ranges. I have tried =SUM(IF((A1:A10=1)*(C1:C10=1),F1:F10,0)) and although the formula result in the formula bar gives me the answer I require (in the live example 114), the cell in which the formula is typed gives a zero value. Any thoughts? Nick Your formula works fine on my test data. Are you sure that the answer is not zero, or that the criteria are correct? Regards Peter >-----Original Message----- >I can use SUMIF with one range but I wish to calculate a >sum based on relevan...

Concatenate function
I am using the following setup to force leading zeroes in my cells (thanks to another poster here) : Format Cells > Custom > 00000 This causes Excel to display any numbers with fewer than 5 digits in a manner such as 00067 (if the original number was 67, for example). My question is this: When using the CONCATENATE(A1&B1) function to join the cells in question, any cells whose source data contained fewer than 5 digits to begin with get Concatenated without those leading zeroes. I tried formatting them as ZIP Codes (automatically adds the leading zeroes), but Concatenate stil...

Can Publisher be used for commercial use?
Yes. -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm Mike, you're a linguist, you speak Gina's language. :) -- Don Vancouver, USA "Mike Koewler" <wordwiz@fuse.net> wrote in message news:4165C306.4070306@fuse.net... > > > > ...

USe a Parameter query
I have a simple query that I wish to use to search for titles of Music CD's. I wish to establish a parameter query that will allow the user to enter a word and the query looks for that word in the title. I have lried .....Like *[Enter the word]* in the query designand various combinations in between but to no avail. Can it be done Thanks George try to use under the "Title filed " Like "*" & ["Enter the Word" ] & "*" so it can filter that word out of your database. "George" wrote: > I have a simple query that I w...

System-wide keyboard hook stops functioning problem !!!
Hi all. I write kinda application that monitors Caps Lock/Scroll Lock/Num Lock status in all system (Thanx Logitech for create keyboards with no indicator lights on upper right corner). Obviously i installed system-wide keyboard hook in dll, which sends messages to main window (modal dialog) whenever the state of these keys changes. Everything works fine at beginning, but after a while, for some odd reason, the dll stops sending messages to the main application. I logged all events in dll to file and really, it does not get any keyboard messages (installed hook procedure is never called...

using post-callouts
Hi All I want to use the post-callouts feature so that when an account is created I can do something in my application. I followed the guidelines given at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm/html/mbs_crmpostcallouts.asp?frame=true I need to put my dll in COM+ on the server where CRM is loaded. Now MS-CRM uses version 1.0 and I have created the dll using .NET version 1.1 So I am not able to register it using regsvcs. Will I have to install .NET 1.1 framework on the server. Will it break MS-CRM application? Is there any other way of doing this. Any help w...

Error executing SSIS package used to populate GP Analysis Cubes
Hi, Our GP databases are installed on SQL Server 2000. I have installed GP Analysis Cubes For Excel on a SQL Server 2005 server. The installation worked without a hitch, however when I try to run the SSIS master package to populate the Data Warehouse database I get an error under the "Run Company" section stating: "Error: Error 0xC0014037 while preparing to load the package. The package is encrypted with a password. The password was not specified, or is not correct." And at the bottom of the execution trail there is a warning to the effect that "the number of ...

Which File to Use
This is a multi-part message in MIME format. ------=_NextPart_000_002E_01C63EAD.21358DE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable My EXCEL crashed and when it recovered, in addition to an original, it = offered me a 7 minute old version and a pretty current "repaired" = version. When it crashed, I was in the midst of mostly inserting and revising = cell comments and miscellaneous formatting stuff. I wonder which is the = better file to use going forward. Is there anyway to find out what the = repair was, which might he...

using error handler on access
Hello there I would like to build error handler that works from all the application. To do that i've build function that is being calls from all the vb code in the application by using On error goto: When i enter the function, is there a way to know which sub or function and object called the function? for example: if the error occur on frmClient at event: Form_AfterUpdate, is there a way to know on the function that the event was on object frmClient and the event was Form_AfterUpdate? hi Roy, Roy Goldhammer wrote: > When i enter the function, is there a way to know which s...

function help needed
I am trying to put together a function for error logging into an sql2005 table. The function will be in the front end of the application. Below is what I have so far that is getting me frustrated. Originally, I had a stored procedure to with the insert which when executed worked fine. Most of it is below the execute line. I am trying to move it into the front end. As of now, I'm not even sure if I'm on the right path or not. The INSERT INTO is the table and the field. The ErrorDate is a datetime and ApplicationSignon is a bit. The remaining fields are varchar. ...