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
    23,195
    627
    113
    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
    40,861
    1,318
    113
    Sterling Heights, MI
    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
    #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,416
    132
    63
    SE WI
    =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
    23,195
    627
    113
    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
    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
    23,195
    627
    113
    Thanks... however 2003. :sad:
     
  8. chadm

    chadm Giving it a go

    Apr 11, 2006
    14,811
    455
    83
    Midwest
    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
    23,195
    627
    113
    SuperCalc!!!!
     
  10. mred

    mred Well-Known Member

    Oct 19, 2006
    5,416
    132
    63
    SE WI
    #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
    Yes Godfather.
     
  12. keepngoal

    keepngoal Jobless Jerk
    Staff Member Bookie

    Jun 20, 2006
    23,195
    627
    113
    Sweet... that is it!!! Thanks mred!! Thank you!

    -keep
     

Share This Page