Results 1 to 12 of 12
  1. #1
    Swarm & Punish
    Points: 290,660, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 28.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    20,241
    Points
    290,660
    Level
    100
    Thumbs Up
    Received: 649
    Given: 1,312

    Brain Dead Excel help

    I am hoping to get some help in an area I am typically proficient in.

    ColumnA | ColumnB
    Date | Number

    I need to look up date ranges (monthly and then quarterly) in ColumnA and sum the appropriate cell in ColumnB.

    I am stuck in a sumif mode....

    =sumif(A1:A24,">01/01/2009",B1:B24) gets me all that are greater than January 1st of this year. However I would like to have the formula look at the criteria for the first quarter from a list of dates in column A: >01/01/2009 AND <04/01/2009

    this does NOT work or any variations thereof:
    =sumif(A1:A24,">01/01/2009"AND "<04/01/2009",B1:B24)

    Do I need an array formula?

    Any help is appreciated.

    -keep


    The first and best victory is to conquer self; to be conquered by self is of all things most shameful and vile. - Plato

    May you only need 39 acres to turn your rig around. - keep

  2. #2
    Speechless
    Points: 489,629, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 44.0%
    Achievements:
    SocialVeteran50000 Experience Points
    jdoggivjc's Avatar
    Join Date
    Sep 2006
    Location
    Sterling Heights, MI
    Posts
    33,031
    Points
    489,629
    Level
    100
    Thumbs Up
    Received: 1,159
    Given: 822

    Re: Brain Dead Excel help

    In the words of Peanut (Jeff Dunham's puppet), "VRROOOOOOOOOOM!!!" (as hand goes over head)


    Chuck Lidell: I paint my toenails with pink and black polish. Problem is, I get more paint on my toes and on the carpet than on my nails. Any advice?
    Maria Sharapova: Don't you beat up other guys for a living? I don't know how to answer this.



  3. #3
    Starter
    Points: 19,682, Level: 42
    Level completed: 82%, Points required for next Level: 168
    Overall activity: 24.0%
    Achievements:
    Veteran10000 Experience Points

    Join Date
    Apr 2008
    Location
    Pants-free Avenue
    Posts
    806
    Points
    19,682
    Level
    42
    Thumbs Up
    Received: 0
    Given: 0

    Re: Brain Dead Excel help

    Have an equation that is the total sum - sum before 01/01/09 - sum after 04/01/09. BAM

    So the final equation would be....

    sum(B1:B24) - sumif(A1:A24,"<01/01/09",B1:B24) - sumif(A1:A24,">04/01/09",B1:B24)


    Last edited by tamjam; 04-14-2009 at 04:18 PM.
    ...Straight going donkey...

  4. #4
    Hall-Of-Famer
    Points: 67,842, Level: 80
    Level completed: 87%, Points required for next Level: 208
    Overall activity: 7.0%
    Achievements:
    Your first GroupVeteran50000 Experience Points
    mred's Avatar
    Join Date
    Oct 2006
    Location
    SE WI
    Posts
    4,953
    Points
    67,842
    Level
    80
    Thumbs Up
    Received: 37
    Given: 0

    Re: Brain Dead Excel help

    =SUMIFS(B1:B24,A1:A24,">01/01/2009",A1:A24,"<04/01/2009")

    EDIT: This is only Excel 2007 or newer. If you have older, I can try to figure out another way.



  5. #5
    Swarm & Punish
    Points: 290,660, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 28.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    20,241
    Points
    290,660
    Level
    100
    Thumbs Up
    Received: 649
    Given: 1,312

    Re: Brain Dead Excel help

    Quote Originally Posted by tamjam View Post
    In one cell you could sumif the date is before 01/01/09 and in another cell sumif the date is after 04/09/09. Then in a third cell the have an question that is the total sum - sum before 01/01/09 - sum after 04/09/09. BAM
    I guess I could do...

    +sum(all dates)-sumif(>date)-sumif(<date)

    kinda seems messy to me .... but it does work. Sometimes I look for the perfect tool (formula) for the task, taking longer to search than what the tasks requires with adequate tools.

    -keep


    The first and best victory is to conquer self; to be conquered by self is of all things most shameful and vile. - Plato

    May you only need 39 acres to turn your rig around. - keep

  6. #6
    Starter
    Points: 19,682, Level: 42
    Level completed: 82%, Points required for next Level: 168
    Overall activity: 24.0%
    Achievements:
    Veteran10000 Experience Points

    Join Date
    Apr 2008
    Location
    Pants-free Avenue
    Posts
    806
    Points
    19,682
    Level
    42
    Thumbs Up
    Received: 0
    Given: 0

    Re: Brain Dead Excel help

    Messy, sure, but it works. You now owe me a favor. I'm not sure when or how, but I will be calling on you for that favor sometime in the future.


    ...Straight going donkey...

  7. #7
    Swarm & Punish
    Points: 290,660, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 28.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    20,241
    Points
    290,660
    Level
    100
    Thumbs Up
    Received: 649
    Given: 1,312

    Re: Brain Dead Excel help

    Quote Originally Posted by mred View Post
    =SUMIFS(B1:B24,A1:A24,">01/01/2009",A1:A24,"<04/01/2009")

    EDIT: This is only Excel 2007 or newer. If you have older, I can try to figure out another way.
    Thanks... however 2003.


    The first and best victory is to conquer self; to be conquered by self is of all things most shameful and vile. - Plato

    May you only need 39 acres to turn your rig around. - keep

  8. #8
    Giving it a go
    Points: 255,061, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 1.0%
    Achievements:
    SocialVeteran50000 Experience Points
    chadm's Avatar
    Join Date
    Apr 2006
    Location
    Midwest
    Posts
    14,610
    Points
    255,061
    Level
    100
    Thumbs Up
    Received: 15
    Given: 7

    Re: Brain Dead Excel help

    Quote Originally Posted by keepngoal View Post
    Thanks... however 2003.
    2003 isn't bad for you. I figured you would be using Lotus123


    I don't know the key to success, but the key to failure is trying to please everyone.

  9. #9
    Swarm & Punish
    Points: 290,660, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 28.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    20,241
    Points
    290,660
    Level
    100
    Thumbs Up
    Received: 649
    Given: 1,312

    Re: Brain Dead Excel help

    SuperCalc!!!!


    The first and best victory is to conquer self; to be conquered by self is of all things most shameful and vile. - Plato

    May you only need 39 acres to turn your rig around. - keep

  10. #10
    Hall-Of-Famer
    Points: 67,842, Level: 80
    Level completed: 87%, Points required for next Level: 208
    Overall activity: 7.0%
    Achievements:
    Your first GroupVeteran50000 Experience Points
    mred's Avatar
    Join Date
    Oct 2006
    Location
    SE WI
    Posts
    4,953
    Points
    67,842
    Level
    80
    Thumbs Up
    Received: 37
    Given: 0

    Re: Brain Dead Excel help

    Got it.

    =SUMPRODUCT((B1:B24),1*(A1:A24>VALUE("1/1/2009")),1*(A1:A24<VALUE("4/1/2009")))

    The "1*" in the equation converts the arrays of booleans into arrays of 0,1 numbers.


    Last edited by mred; 04-14-2009 at 04:58 PM.

  11. #11
    All-Star
    Points: 18,636, Level: 41
    Level completed: 66%, Points required for next Level: 314
    Overall activity: 0%
    Achievements:
    Veteran10000 Experience Points
    TxCycloneFan's Avatar
    Join Date
    Oct 2006
    Location
    Bastrop TX
    Posts
    1,483
    Points
    18,636
    Level
    41
    Thumbs Up
    Received: 0
    Given: 0

    Re: Brain Dead Excel help

    Quote Originally Posted by tamjam View Post
    Messy, sure, but it works. You now owe me a favor. I'm not sure when or how, but I will be calling on you for that favor sometime in the future.
    Yes Godfather.


    Go Clones!!
    Gig 'em Aggies!!
    Saw 'em off!!

  12. #12
    Swarm & Punish
    Points: 290,660, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 28.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    20,241
    Points
    290,660
    Level
    100
    Thumbs Up
    Received: 649
    Given: 1,312

    Re: Brain Dead Excel help

    Quote Originally Posted by mred View Post
    Got it.

    =SUMPRODUCT((B1:B24),1*(A1:A24>VALUE("1/1/2009")),1*(A1:A24<VALUE("4/1/2009")))

    The "1*" in the equation converts the arrays of booleans into arrays of 0,1 numbers.
    Sweet... that is it!!! Thanks mred!! Thank you!

    -keep


    The first and best victory is to conquer self; to be conquered by self is of all things most shameful and vile. - Plato

    May you only need 39 acres to turn your rig around. - keep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • TV: FS1
  • FOOTBALL: Iowa State vs. West Virginia
  • November 29, 2014
  • 11:00 AM