Brain Dead Excel help

Discussion in 'Off-Topic' started by keepngoal, Apr 14, 2009.

  1. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    24,717
    1,130
    113
    Ratings:
    +2,522 / 55 / -0
    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
     
    • Like Like x 1
  2. jdoggivjc

    jdoggivjc Well-Known Member

    Sep 27, 2006
    45,070
    2,212
    113
    Publications Manager
    Macomb, MI
    Ratings:
    +4,906 / 103 / -1
    In the words of Peanut (Jeff Dunham's puppet), "VRROOOOOOOOOOM!!!" (as hand goes over head)
     
  3. tamjam

    tamjam Active Member

    Apr 18, 2008
    806
    71
    28
    Telephone Poacher
    Pants-free Avenue
    Ratings:
    +71 / 0 / -0
    #3 tamjam, Apr 14, 2009
    Last edited: Apr 14, 2009
    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)
     
    • Like Like x 1
  4. mred

    mred Well-Known Member

    Oct 19, 2006
    5,552
    225
    63
    SE WI
    Ratings:
    +292 / 0 / -0
    =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.
     
    • Like Like x 1
  5. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    24,717
    1,130
    113
    Ratings:
    +2,522 / 55 / -0
    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. :no:

    -keep
     
  6. tamjam

    tamjam Active Member

    Apr 18, 2008
    806
    71
    28
    Telephone Poacher
    Pants-free Avenue
    Ratings:
    +71 / 0 / -0
    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.
     
    • Like Like x 1
  7. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    24,717
    1,130
    113
    Ratings:
    +2,522 / 55 / -0
    Thanks... however 2003. :sad:
     
  8. chadm

    chadm Giving it a go

    Apr 11, 2006
    15,385
    583
    113
    Midwest
    Ratings:
    +1,072 / 74 / -0
    2003 isn't bad for you. I figured you would be using Lotus123:cute:
     
    • Like Like x 1
  9. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    24,717
    1,130
    113
    Ratings:
    +2,522 / 55 / -0
    SuperCalc!!!!
     
  10. mred

    mred Well-Known Member

    Oct 19, 2006
    5,552
    225
    63
    SE WI
    Ratings:
    +292 / 0 / -0
    #10 mred, Apr 14, 2009
    Last edited: Apr 14, 2009
    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.
     
    • Like Like x 1
  11. TxCycloneFan

    TxCycloneFan Well-Known Member

    Oct 19, 2006
    1,484
    54
    48
    none
    Bastrop TX
    Ratings:
    +54 / 0 / -0
    Yes Godfather.
     
  12. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    24,717
    1,130
    113
    Ratings:
    +2,522 / 55 / -0
    Sweet... that is it!!! Thanks mred!! Thank you!

    -keep
     

Share This Page