Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I would like to sum the values in column D where the values/text in columns A, B, and C are "A". ""John", and the text in column A is either 2/1/2008 or 5/1/2008.
    Any ideas?
    Thanks in advance.
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't understand the requirement

    "... where the values/text in columns A, B, and C are "A". ""John", and the text in column A is either 2/1/2008 or 5/1/2008"

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Something like
    =SUMPRODUCT((D126),--(A1:A26="A"),--(B1:B26="John"),--(C1:C26=DATEVALUE("02/01/2008")))+SUMPRODUCT((D126),--(A1:A26="A"),--(B1:B26="John"),--(C1:C26=DATEVALUE("05/01/2008")))
    You'll need to watch the Date formats. I'm sure there is a neater OR solution though.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='mdmackillop' post='767911' date='27-Mar-2009 21:32']Something like
    =SUMPRODUCT((D126),--(A1:A26="A"),--(B1:B26="John"),--(C1:C26=DATEVALUE("02/01/2008")))+SUMPRODUCT((D126),--(A1:A26="A"),--(B1:B26="John"),--(C1:C26=DATEVALUE("05/01/2008")))
    You'll need to watch the Date formats. I'm sure there is a neater OR solution though.[/quote]

    Thanks Mack

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is not the answer but it is close. Assume the data is in rows 2:10, the formula below works but it will only check for one date - there may be a way to make it check two dates but I don't know how.

    =SUMIFS(D210,A2:A10,"=A",B2:B10,"=JOHN",C2:C10,"=2/1/2008")



    [quote name='jlkirk' post='767899' date='27-Mar-2009 17:47']I would like to sum the values in column D where the values/text in columns A, B, and C are "A". ""John", and the text in column A is either 2/1/2008 or 5/1/2008.
    Any ideas?
    Thanks in advance.
    Jeff[/quote]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Keep in mind that SUMIFS will work in Excel 2007 (or later) only.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To do an OR, Just add the 2 sumifs:

    =SUMIFS(D210,A2:A10,"=A",B2:B10,"=JOHN",C2:C10,"=2/1/2008") +SUMIFS(D210,A2:A10,"=A",B2:B10,"=JOHN",C2:C10,"=5/1/2008")

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cool,

    I was thinking it might even be easier to use autofilter and sum col D after filtering.

    [quote name='sdckapr' post='768079' date='29-Mar-2009 04:07']To do an OR, Just add the 2 sumifs:

    =SUMIFS(D210,A2:A10,"=A",B2:B10,"=JOHN",C2:C10,"=2/1/2008") +SUMIFS(D210,A2:A10,"=A",B2:B10,"=JOHN",C2:C10,"=5/1/2008")

    Steve[/quote]

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That would be fine if you wanted the number to be more live. Then SUBTOTAL would be used and it would calculate based on filtered rows. I presume the OP wanted something not just based on the filter, perhaps to make a table of various combinations (though in this case a pivot table might be the most efficient option...)

    Steve

Posting Permissions

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