+ Reply to Thread
Results 1 to 13 of 13

sum product to count data in a date list

  1. 02-23-2010, 11:16 AM #1
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    sum product to count data in a date list

    Hi All,

    I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...

    I now need to amend the formula to count how many times a date appears in a range of dates.

    I know when you use the sumproduct...dates must be input and formatted as text.

    example

    A1
    01-Jan
    02-Jan
    5-Feb
    7-Feb
    12-Feb

    Basically i need the formula to only count dates with feb in.

    Help on this would be appreciated.

    Thanks
    Last edited by thompsy121; 02-24-2010 at 04:14 AM.
    Reply With QuoteRegister To Reply
  2. 02-23-2010, 11:18 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    Perhaps?

    =Sumproduct(--(TEXT(A1:A100,"mmm")="Feb"))

    Adjust ranges to suit.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 02-23-2010, 11:26 AM #3
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    Thanks NBVC exactly what i needed.

    Out of curiosity and for future formulas...what is the "mmm" used for?

    Thanks
    Reply With QuoteRegister To Reply
  4. 02-23-2010, 11:31 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    In the TEXT() function, it converts the date to a text format of the shortened month name... ie. "Feb".

    See the TEXT() function for more in Excel Help...
    Reply With QuoteRegister To Reply
  5. 02-23-2010, 11:42 AM #5
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    Thanks for that...

    One last one for you...unsure if this is possible.

    As previous i have been using sumproducts a lot to find certain information in a list.

    How could i amend to suite the following:

    A1 - B1
    01-Jan - Tom
    02-Jan - Jim
    5-Feb - Jim
    7-Feb - Tom
    12-Feb - Tom

    (please excuse formatting)

    I have used the following formula to count how many times Tom appears with a date next to his name...for this exmaple i have put dates next to all of them...

    =SUMPRODUCT((B1:B2000="Tom")*ISNUMBER(DATEVALUE(A1:A2000))

    What i now need to do is amend the formula so that it still counts Tom with dates...but only count the "Feb" dates.

    Does this make sense.

    Thanks
    Reply With QuoteRegister To Reply
  6. 02-23-2010, 11:44 AM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    =Sumproduct((B1:B2000="Tom")*(TEXT(A1:A2000,"mmm")="Feb"))
    Reply With QuoteRegister To Reply
  7. 02-23-2010, 12:05 PM #7
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    Brilliant,

    Thanks...worked a treat.

    One last thing is possible...

    A1 - B1 - C1
    01-Jan - Tom - £100
    02-Jan - Jim - £150
    5-Feb - Jim - £200
    7-Feb - Tom - £80
    12-Feb - Tom - £100

    i need one last colum to count the sum in C where Tom has a feb date - total = £180.

    =Sumproduct((B1:B2000="Tom")*(TEXT(A1:A2000,"mmm")="Feb"))*(C1:C2000))

    C is formatted as currency.

    Currently i am getting #N/A

    Thanks
    Reply With QuoteRegister To Reply
  8. 02-23-2010, 12:10 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    Are you getting #N/A or #VALUE?

    What happens with this?

    =SUMPRODUCT((B1:B2000="Tom")*(TEXT(A1:A2000,"mmm")="Feb"),(C1:C2000))

    if still #N/A.. then do you have that error in any of the columns?
    Reply With QuoteRegister To Reply
  9. 02-23-2010, 12:44 PM #9
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    I am now getting the #VALUE?

    Thanks
    Reply With QuoteRegister To Reply
  10. 02-23-2010, 12:48 PM #10
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    Can I see a spreadsheet sample?
    Reply With QuoteRegister To Reply
  11. 02-23-2010, 01:03 PM #11
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    Thanks for this. Hope this helps.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 02-23-2010, 01:23 PM #12
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sum product to count data in a date list

    That formula is not what I gave you.....

    You had brackets in wrong places...

    Also, you were refering to G8 instead of F8 for name to get...

    Try:

    =SUMPRODUCT((B21:B41=F8)*(TEXT(A21:A41,"mmm")="Feb")*(C21:C41))

    copied down.
    Reply With QuoteRegister To Reply
  13. 02-24-2010, 04:13 AM #13
    thompsy121
    thompsy121 is offline
    Forum Contributor thompsy121's Avatar
    Join Date
    09-25-2008
    Location
    Newcastle upon Tyne - UK
    MS-Off Ver
    Office 365
    Posts
    119

    Re: sum product to count data in a date list

    Brilliant,

    Sorry...typo on my side...cant believe i missed that. Was a bit of a rush job before i finished work. Did not have time to check through the formula piece by piece.

    Thanks for all your help with this one.

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1