Results 1 to 12 of 12
  1. #1
    Jobless Jerk
    Points: 332,364, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 24.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    22,617
    Points
    332,364
    Level
    100
    Thumbs Up
    Received: 2,342
    Given: 5,503

    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


    This post is pending approval of site sponsors.

  2. #2
    Speechless
    Points: 542,641, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 55.0%
    Achievements:
    SocialVeteran50000 Experience PointsOverdrive
    jdoggivjc's Avatar
    Join Date
    Sep 2006
    Location
    Sterling Heights, MI
    Posts
    39,310
    Points
    542,641
    Level
    100
    Thumbs Up
    Received: 3,803
    Given: 2,838

    Re: Brain Dead Excel help

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


    "Success is just failure that hasn't happened yet." - life advice from Latrell Spreewell



  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 03:18 PM.
    ...Straight going donkey...

  4. #4
    Addict
    Points: 76,074, Level: 85
    Level completed: 72%, Points required for next Level: 476
    Overall activity: 14.0%
    Achievements:
    Your first GroupVeteran50000 Experience Points
    mred's Avatar
    Join Date
    Oct 2006
    Location
    SE WI
    Posts
    5,352
    Points
    76,074
    Level
    85
    Thumbs Up
    Received: 193
    Given: 2

    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
    Jobless Jerk
    Points: 332,364, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 24.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    22,617
    Points
    332,364
    Level
    100
    Thumbs Up
    Received: 2,342
    Given: 5,503

    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


    This post is pending approval of site sponsors.

  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
    Jobless Jerk
    Points: 332,364, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 24.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    22,617
    Points
    332,364
    Level
    100
    Thumbs Up
    Received: 2,342
    Given: 5,503

    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.


    This post is pending approval of site sponsors.

  8. #8
    Giving it a go
    Points: 259,520, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 16.0%
    Achievements:
    SocialVeteran50000 Experience Points
    chadm's Avatar
    Join Date
    Apr 2006
    Location
    Midwest
    Posts
    14,779
    Points
    259,520
    Level
    100
    Thumbs Up
    Received: 140
    Given: 107

    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
    Jobless Jerk
    Points: 332,364, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 24.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    22,617
    Points
    332,364
    Level
    100
    Thumbs Up
    Received: 2,342
    Given: 5,503

    Re: Brain Dead Excel help

    SuperCalc!!!!


    This post is pending approval of site sponsors.

  10. #10
    Addict
    Points: 76,074, Level: 85
    Level completed: 72%, Points required for next Level: 476
    Overall activity: 14.0%
    Achievements:
    Your first GroupVeteran50000 Experience Points
    mred's Avatar
    Join Date
    Oct 2006
    Location
    SE WI
    Posts
    5,352
    Points
    76,074
    Level
    85
    Thumbs Up
    Received: 193
    Given: 2

    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 03:58 PM.

  11. #11
    All-Star
    Points: 19,882, Level: 43
    Level completed: 4%, Points required for next Level: 868
    Overall activity: 0%
    Achievements:
    Veteran10000 Experience Points
    TxCycloneFan's Avatar
    Join Date
    Oct 2006
    Location
    Bastrop TX
    Posts
    1,484
    Points
    19,882
    Level
    43
    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
    Jobless Jerk
    Points: 332,364, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 24.0%
    Achievements:
    SocialVeteranCreated Album pictures50000 Experience Points
    keepngoal's Avatar
    Join Date
    Jun 2006
    Posts
    22,617
    Points
    332,364
    Level
    100
    Thumbs Up
    Received: 2,342
    Given: 5,503

    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


    This post is pending approval of site sponsors.

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: TBA
  • Football Season Opener vs. UNI
  • September 3, 2016
  • 07:00 PM