+ Reply to Thread
Results 1 to 15 of 15

Add only values that contain "d" and in the same month

  1. #1
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Add only values that contain "d" and in the same month

    Hi all,

    I am going mad trying to resolve this!

    I have dates in column A, and the ability to enter a reference of either "d" or "w" in column C. In column D a 'number of times' can be enter. So for example:

    Col A Col C Col D
    01/01/2011 d 5
    01/01/2011 d 6
    01/01/2011 w 9
    02/01/2011 d 4
    10/02/2011 d 4
    11/02/2011 w 5
    11/02/2011 d 5
    11/02/2011 w 9

    I want to return in Column E that there have been 15 "ds" in January, 9 "ws" in January, 9 "ds" in February, and 14 "ws" in February.

    Any help will be greatly appreciated!
    Last edited by DonkeyOte; 02-11-2011 at 08:31 AM. Reason: "Please Help, " removed from title - adds no value

  2. #2
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Please Help, Add only values that contain "d" and in the same month

    Sorry sheet attached for clarity.
    Attached Files Attached Files

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,389

    Re: Please Help, Add only values that contain "d" and in the same month

    Hi,

    =SUMPRODUCT((MONTH(A1:A8)=1)*(C1:C8="d")*(D1:D8))

    will return the total d's for January.

    Adjust MONTH(A1:A8)=1 to MONTH(A1:A8)=2 for February.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Please Help, Add only values that contain "d" and in the same month

    Thanks,

    Exactly what I wanted. It appears I really have a lot to learn!

  5. #5
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Please Help, Add only values that contain "d" and in the same month

    Sorry, just encountered a further problem.

    When the date rolls over to next jan (month 13) this is not recognised by excel?

  6. #6
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Month 13 (i.e. 12+1,2,3 etc.)

    I am using the follow formula kindly provided by Sweep.

    =SUMPRODUCT((MONTH($A$15:$A$10000)=AN22)*($C$15:$C$10000="d")*($D$15:$D$10000))

    In this formula AN22 contains the month reference (2 in this case for Feb), AN23 would contain 3 for March. The problem is that AN33 contains number 13 and excel doesn't like it. I somehow need to include the year ref in this but don't know how. Any ideas?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    XL2007 onwards you should really be using SUMIFS rather than SUMPRODUCT.

    Perhaps you could post a sample file to illustrate your requirements, how you wish to incorporate "year" and indeed how "year" is to be determined

    In your sample be sure to outline expected results.

    Once we have a better idea of your requirements we may also look to retitle your thread.

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    If you put a date in AN22, 1st of the month that you want to calculate, then you could use this version

    =SUMPRODUCT((MONTH($A$15:$A$10000)=MONTH(AN22))*(YEAR($A$15:$A$10000)=YEAR(AN22))*($C$15:$C$10000="d"),$D$15:$D$10000)

    or as Donkeyote says, you can use SUMIFS, i.e.

    =SUMIFS($D$15:$D$10000,$A$15:$A$10000,">="&AN22,$A$15:$A$10000,"<="&EOMONTH(AN22,0),$C$15:$C$10000, "d")
    Audere est facere

  9. #9
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    Please see attached sheet
    Attached Files Attached Files

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    Adapting the SUMIFS previously provided:

    Please Login or Register  to view this content.
    Assuming the dates are to increment:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,389

    Re: Please Help, Add only values that contain "d" and in the same month

    Quote Originally Posted by Excel_Monkey
    Sorry, just encountered a further problem.

    When the date rolls over to next jan (month 13) this is not recognised by excel?
    So, you need it to be year specific? Add another term to the sumproduct....

    =SUMPRODUCT((YEAR(A1:A8)=2011)*(MONTH(A1:A8)=1)*(C1:C8="d")*(D1:D8))

    The MONTH() function returns a number between 1 and 12 that represents the month portion of the date. It won't return 13.
    Last edited by DonkeyOte; 02-11-2011 at 08:30 AM. Reason: added quote re: merged threads

  12. #12
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    Sorry, I can't make this work. See adapted sheet.
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-11-2011 at 08:22 AM. Reason: removed unnecessary quote

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Month 13 (i.e. 12+1,2,3 etc.)

    Quote Originally Posted by Excel_Monkey
    Sorry, I can't make this work. See adapted sheet.
    Perhaps try the suggested formula ... ie N$14 not N$16 (you want to reference D/W dynamically)
    Last edited by DonkeyOte; 02-11-2011 at 08:29 AM. Reason: added quote

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Please Help, Add only values that contain "d" and in the same month

    @Excel_Monkey

    Please do not create duplicates of the same question - as you can see this leads to confusion and potentially wastes peoples time (freely donated)

    I've merged the threads but as you can see some posts now appear out of sequence.

    Stick to one thread going forward - if you need to clarify requirements do so by replying to your original thread (only)

  15. #15
    Registered User
    Join Date
    02-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Add only values that contain "d" and in the same month

    My Apologies - I am new on here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1