Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SUMIFS Based on Two Conditions (2007)

    I’m trying to do a SUMIFS statement using Excel 2007 and I’m have a difficult time getting the formula correct. Here’s how my worksheet looks:
    Column A is a date filed that’s formatted mm/dd/yyyy
    Column B is a test field that has names (e.g., Steve, Sam, Lucy, etc.)
    Column C is a number filed
    All the data input starts on row 4 and goes through row 40.
    I would like my formula to look at column A and find all of the dates in January; then I would like the formula to look at column B and find all of the fields with Steve’s name, then provide me a sum of all Steve’s points in January.
    Here’s the formulas that I used:
    =SUMIFS(C4:C20,A4:A20,”01/**/****”,B4:B20,”Steve”)
    The formula returns either returns a 0 value or a #VALUE! Error. I have attached a copy of the worksheet. Any assistance is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIFS Based on Two Conditions (2007)

    I don't have 2007 so this is untested. It looks like the dates in A4:A20 are Excel serial dates, rather than the text that formula is testing against.
    =SUMIFS(C4:C20,A4:A20,">="&DATEVALUE(”01/01/2008”),B4:B20,”Steve”)
    I don't know if
    =SUMIFS(C4:C20,A4:A20,">="&DATEVALUE(”01/01/2008”),A4:A20,"<"&DATEVALUE(”02/01/2008”),B4:B20,”Steve”) will be accepted, but
    =SUMIFS(C4:C20,A4:A20,"<"&DATEVALUE(”02/01/2008”),B4:B20,”Steve”) - SUMIFS(C4:C20,A4:A20,"<"&DATEVALUE(”01/01/2008”),B4:B20,”Steve”) should work.

    If you want January of any year,
    =SUMPRODUCT(C4:C20,--(MONTH(A4:A20),--(B4:B20="Steve"))

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIFS Based on Two Conditions (2007)

    Shouldn't

    =SUMPRODUCT(C4:C20,--(MONTH(A4:A20),--(B4:B20="Steve"))

    be

    =SUMPRODUCT(C4:C20,--(MONTH(A4:A20)=1),--(B4:B20="Steve"))

    or

    =SUMPRODUCT(C4:C20,(MONTH(A4:A20)=1)*(B4:B20="Stev e"))

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: SUMIFS Based on Two Conditions (2007)

    Mikerickson and Hans,
    Thanks for your help. The SUMPRODUCT formual worked perfect!

Posting Permissions

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