+ Reply to Thread
Results 1 to 11 of 11

Date conditioned counting

  1. 07-28-2017, 10:30 AM #1
    Vicente Rojas
    Vicente Rojas is offline
    Registered User
    Join Date
    07-28-2017
    Location
    Chile
    MS-Off Ver
    365
    Posts
    9

    Exclamation Date conditioned counting

    I have 2 types of people; givers and recievers. What i need to know is how many "recievers" become first time "givers" . To know this i need to take into account the date of the transaction.

    My data looks as follows
    Giver Reciever Date
    Nuri James 1-1-17
    John Carin 1-1-17
    Carin Liam 1-2-17
    Karol Nuri 1-3-17
    Liam Nuri 1-3-17
    Nuri Charles 1-3-17


    So with this data i should get that 2 "transitions" happened
    - John, karol and Nuri are original givers so they shouldnt count
    - James, Charles never became "givers" so they shouldnt count.
    - Carin and Liam became "givers" AFTER being "recievers" so they should count

    I hope you guys can help me help me, I need to get this data.
    Reply With QuoteRegister To Reply
  2. 07-28-2017, 10:34 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    Just one question: is it possible for a receiver to become a giver on the same date they received?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-28-2017, 10:37 AM #3
    Vicente Rojas
    Vicente Rojas is offline
    Registered User
    Join Date
    07-28-2017
    Location
    Chile
    MS-Off Ver
    365
    Posts
    9

    Re: Date conditioned counting

    Yes it is! Sorry, I forgot to mention that I also have a column for time.

    Thanks.
    Reply With QuoteRegister To Reply
  4. 07-28-2017, 10:40 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    Probably don't need the time. How about this with a helper column, that could be hidden:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    		 Giver Reciever Date
    2
    		 Nuri James
    01/01/2017
    3
    		 John Carin
    01/01/2017
    TRUE
    4
    		 Carin Liam
    01/02/2017
    TRUE
    5
    		 Karol Nuri
    01/03/2017
    FALSE
    6
    		 Liam Nuri
    01/03/2017
    FALSE
    7
    		 Nuri Charles
    01/03/2017
    8
    2
    		 Transitions
    Sheet: Sheet2

    Excel 2016 (Windows) 32 bit
    E
    2
    		 =IFNA(MATCH(B2,$A$2:$A$7,0)>ROW()-1,"")
    8
    =COUNTIF($E$2:$E$7,TRUE)
    Sheet: Sheet2
    Reply With QuoteRegister To Reply
  5. 07-28-2017, 11:17 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  6. 07-28-2017, 11:19 AM #6
    Vicente Rojas
    Vicente Rojas is offline
    Registered User
    Join Date
    07-28-2017
    Location
    Chile
    MS-Off Ver
    365
    Posts
    9

    Re: Date conditioned counting

    For some reason, it is not working for me. Also i dont know what it means when the cell stays black. Sorry
    Reply With QuoteRegister To Reply
  7. 07-28-2017, 11:23 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    I don't know what you mean about a cell staying black, sorry. Working version attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-28-2017, 11:24 AM #8
    Vicente Rojas
    Vicente Rojas is offline
    Registered User
    Join Date
    07-28-2017
    Location
    Chile
    MS-Off Ver
    365
    Posts
    9

    Re: Date conditioned counting

    sorry, i meant "blank"
    Reply With QuoteRegister To Reply
  9. 07-28-2017, 11:25 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    That's where there is no match - James and Charles do not appear in column A at all, so are not relevant to the calculation.
    Reply With QuoteRegister To Reply
  10. 07-28-2017, 11:29 AM #10
    Vicente Rojas
    Vicente Rojas is offline
    Registered User
    Join Date
    07-28-2017
    Location
    Chile
    MS-Off Ver
    365
    Posts
    9

    Re: Date conditioned counting

    Got it to work, thank you very much!
    Reply With QuoteRegister To Reply
  11. 07-28-2017, 11:29 AM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Date conditioned counting

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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)

Similar Threads

  1. [SOLVED] conditioned Median
    By MikaelChristiansen in forum Excel General
    Replies: 4
    Last Post: 02-23-2017, 07:47 AM
  2. [SOLVED] Drop-down list, conditioned static date and time
    By Inkarn in forum Excel General
    Replies: 6
    Last Post: 12-19-2013, 03:19 PM
  3. value according last conditioned value
    By MartyZ in forum Excel General
    Replies: 1
    Last Post: 03-12-2012, 04:17 AM
  4. use if conditioned to several conditions
    By silviario in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2011, 03:34 AM
  5. Sheet conditioned formulas
    By dhrddm in forum Excel General
    Replies: 1
    Last Post: 01-26-2011, 02:58 AM
  6. Question about conditioned list
    By Oscar Garcia in forum Excel General
    Replies: 0
    Last Post: 05-02-2007, 11:10 AM
  7. conditioned functions
    By John in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2006, 11:55 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1