+ Reply to Thread
Results 1 to 4 of 4

Exclude date from sumif

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    aus
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Exclude date from sumif

    Hi there,
    I have a situation where i need to exclude from a column, a cell with a date and sumif the rest of the numbers. I do know that excel treats date as numbers. How do i write the sumif statement in this case?
    Example.

    1
    10
    1
    10
    12/31/2012
    1
    10
    1
    10

    Generous help is appreciated...

  2. #2
    Registered User
    Join Date
    10-16-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Exclude date from sumif

    The value of 01/01/2012 is 40909, 02/01/2012 is 40910 and so on,

    if you know that your values will be smaller than that, what I would do is simply =sumif(range,"<40910")

    Otherwise a simple vba code can do what you want. Without vba, I wouldn't know, sorry.

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Exclude date from sumif

    Assuming your data are in column A starting from A1.

    In B1 and copy down, put this.

    =YEAR(A1)

    This will be a helper and hidden column.

    Then tis formula will do the job.

    =SUMPRODUCT((B1:B10=1900)*(A1:A10))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    aus
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Exclude date from sumif

    Thank you everyone.
    Sumif did provide a better performance compared to sumproduct but the weird thing is that sumproduct did not calculate anything and gave me a 0 value.
    Sumif works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1